日期：2012-07-30 14:59:00 来源：本站整理
一列保存多个ID(将多个用逗号离隔的ID转换成用逗号离隔的名称)[MSSQL防范]
本文“一列保存多个ID(将多个用逗号离隔的ID转换成用逗号离隔的名称)[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
后台:在做项目时,常常会碰到这样的表构造在主表的中有一列保存的是用逗号离隔ID.如,当一个员工从属多个部门时、当一个项目从属多个城市时、当一个设备从属多个项目时,很多人城市在员工表中加入一个deptIds VARCHAR(1000)列（本文以员工从属多个部门为例）,用以保存部门编号列表（很明显这不符合第一范式,但很多人这样计划了,在这篇文章中我们暂不谈论在这种利用场景下,如此计划的对与错,有爱好的可以在答复中聊聊）,然后我们在查询列表中需求看到这个员工从属哪些部门.
初始化数据:
部门表、员工表数据:
复制代码 代码以下:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]'))
DROP TABLE [dbo].Department
GO
--部门表
CREATE TABLE Department
(
id int,
name nvarchar(50)
)
INSERT INTO Department(id,name)
SELECT 1,'人事部'
UNION
SELECT 2,'工程部'
UNION
SELECT 3,'管理部'
SELECT * FROM Department
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]'))
DROP TABLE [dbo].Employee
GO
--员工表
CREATE TABLE Employee
(
id int,
name nvarchar(20),
deptIds varchar(1000)
)
INSERT INTO Employee(id,name,deptIds)
SELECT 1,'蒋大华','1,2,3'
UNION
SELECT 2,'小明','1'
UNION
SELECT 3,'小华',''
SELECT * FROM Employee
但愿得到的后果:
办理办法:第一步,是得到以下的数据.行将员工表调集与相关的部门调集做穿插衔接,此中利用了fun_SplitIds函数（作用是将ids分割成id列表）,然后员工调集与这个得到的调集做穿插衔接
复制代码 代码以下:
SELECT E.*,ISNULL(D.name,'') AS deptName
FROM Employee AS E
OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID
LEFT JOIN Department AS D ON DID.ID=D.id;
第二步,已经得到了如上的数据,然后要做的就是按照ID分组,并对deptName列做聚合操作,但惋惜的是SQL SERVER还没有供应对字符串做聚合的操作.但想到,我们处理树形构造数据时,用CTE来做关系数据,做成有树形格局的数据,如此我们也可以将这个问题转换成做树形格局的问题,代码以下:
复制代码 代码以下:
;WITH EmployeT AS(
--员工的基本信息(利用OUTER APPLY将多个ID拆脱离来,然后与部门表相关联)
--此时已将员工表所存的IDS辨别与部门相关联,下面需求将此调集合的deptName聚合成一个记录
SELECT E.*,ISNULL(D.name,'') AS deptName
FROM Employee AS E
OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID
LEFT JOIN Department AS D ON DID.ID=D.id
),mike AS(
SELECT id,name,deptIds,deptName
,ROW_NUMBER()OVER(PARTITION BY id ORDER BY id) AS level_num
FROM EmployeT
),mike2 AS(
SELECT id,name,deptIds,CAST(deptName AS NVARCHAR(100)) AS deptName,level_num
FROM mike
WHERE level_num=1
UNION ALL
SELECT m.id,m.name,m.deptIds,CAST(m2.deptName+','+m.deptName AS NVARCHAR(100)) AS deptName,m.level_num
FROM mike AS m
INNER JOIN mike2 AS m2 ON m.ID=m2.id AND m.level_num=m2.level_num+1
),maxMikeByIDT AS(
SELECT id,MAX(level_num) AS level_num
FROM mike2
GROUP BY ID
)
SELECT A.id,A.name,A.deptIds,A.deptName
FROM mike2 AS A
INNER JOIN maxMikeByIDT AS B ON A.id=B.ID AND A.level_num=B.level_num
ORDER BY A.id OPTION (MAXRECURSION 0)
后果以下:
全部SQL:复制代码 代码以下:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]'))
DROP TABLE [dbo].Department
GO
--部门表
CREATE TABLE Department
(
id int,
name nvarchar(50)
)
INSERT INTO Department(id,name)
SELECT 1,'人事部'
UNION
SELECT 2,'工程部'
UNION
SELECT 3,'管理部'
SELECT * FROM Department
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]'))
DROP TABLE [dbo].Employee
GO
--员工表
CREATE TABLE Employee
(
id int,
name nvarchar(20),
deptIds varchar(1000)
)
INSERT INTO Employee(id,name,deptIds)
SELECT 1,'蒋大华','1,2,3'
UNION
SELECT 2,'小明','1'
UNION
SELECT 3,'小华',''
SELECT * FROM Employee
--成立一个表值函数,用来拆分用逗号分割的数字串,返回只有一列数字的表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_SplitIds]'))
DROP FUNCTION [dbo].fun_SplitIds
GO
CREATE FUNCTION dbo.fun_SplitIds(
@Ids nvarchar(1000)
)
RETURNS @t_id TABLE (id VARCHAR(36))
AS
BEGIN
DECLARE @i INT,@j INT,@l INT,@v VARCHAR(36);
SET @i = 0;
SET @j = 0;
SET @l = len(@Ids);
while(@j < @l)
begin
SET @j = charindex(',',@Ids,@i+1);
IF(@j = 0) set @j = @l+1;
SET @v = cast(SUBSTRING(@Ids,@i+1,@j-@i-1) as VARCHAR(36));
INSERT INTO @t_id VALUES(@v)
SET @i = @j;
END
RETURN;
END
GO
;WITH EmployeT AS(
--员工的基本信息(利用OUTER APPLY将多个ID拆脱离来,然后与部门表相关联)
--此时已将员工表所存的IDS辨别与部门相关联,下面需求将此调集合的deptName聚合成一个记录
SELECT E.*,ISNULL(D.name,'') AS deptName
FROM Employee AS E
OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID
LEFT JOIN Department AS D ON DID.ID=D.id
),mike AS(
SELECT id,name,deptIds,deptName
,ROW_NUMBER()OVER(PARTITION BY id ORDER BY id) AS level_num
FROM EmployeT
),mike2 AS(
SELECT id,name,deptIds,CAST(deptName AS NVARCHAR(100)) AS deptName,level_num
FROM mike
WHERE level_num=1
UNION ALL
SELECT m.id,m.name,m.deptIds,CAST(m2.deptName+','+m.deptName AS NVARCHAR(100)) AS deptName,m.level_num
FROM mike AS m
INNER JOIN mike2 AS m2 ON m.ID=m2.id AND m.level_num=m2.level_num+1
),maxMikeByIDT AS(
SELECT id,MAX(level_num) AS level_num
FROM mike2
GROUP BY ID
)
SELECT A.id,A.name,A.deptIds,A.deptName
FROM mike2 AS A
INNER JOIN maxMikeByIDT AS B ON A.id=B.ID AND A.level_num=B.level_num
ORDER BY A.id OPTION (MAXRECURSION 0)
以上是“一列保存多个ID(将多个用逗号离隔的ID转换成用逗号离隔的名称)[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“一列保存多个ID(将多个用逗号离隔的ID转换成用逗号离隔的名称)”更多相关内容
中查找“一列保存多个ID(将多个用逗号离隔的ID转换成用逗号离隔的名称)”更多相关内容评论内容只代表网友观点，与本站立场无关！
评论摘要(共 0 条，得分 0 分，平均 0 分)
查看完整评论