begin
FROM mike
SELECT id,MAX(level_num) AS level_num
GO
CREATE TABLE Employee
name nvarchar(50)
CREATE TABLE Department
SET @v = cast(SUBSTRING(@Ids,@i+1,@j-@i-1) as VARCHAR(36));

UNION
全部SQL：
UNION ALL
FROM mike AS m

SELECT * FROM Employee
RETURNS @t_id TABLE (id VARCHAR(36))
FROM mike AS m
SELECT * FROM Department
id int,
deptIds varchar(1000)
RETURN;
UNION
UNION
SET @l = len(@Ids);
INSERT INTO Employee(id,name,deptIds)
INSERT INTO Department(id,name)

SELECT m.id,m.name,m.deptIds,CAST(m2.deptName+','+m.deptName AS NVARCHAR(100)) AS deptName,m.level_num
DROP TABLE [dbo].Employee
(
--员工的基本信息(使用OUTER APPLY将多个ID拆分开来,然后与部门表相关联)

结果如下：

INNER JOIN maxMikeByIDT AS B ON A.id=B.ID AND A.level_num=B.level_num
DROP TABLE [dbo].Department
UNION
--员工表
LEFT JOIN Department AS D ON DID.ID=D.id

(
SELECT id,name,deptIds,deptName

--员工表

OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID

INSERT INTO Department(id,name)
)
--部门表
UNION
),maxMikeByIDT AS(

解决方法：

希望得到的结果：

SELECT A.id,A.name,A.deptIds,A.deptName
OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID

),mike AS(

END
背景：在做项目时，经常会遇到这样的表结构在主表的中有一列保存的是用逗号隔开ID。如，当一个员工从属多个部门时、当一个项目从属多个城市时、当一个设备从属多个项目时，很多人都会在员工表中加入一个deptIds VARCHAR(1000)列（本文以员工从属多个部门为例），用以保存部门编号列表（很明显这不符合第一范式，但很多人这样设计了，在这篇文章中我们暂不讨论在这种应用场景下，如此设计的对与错，有兴趣的可以在回复中聊聊），然后我们在查询列表中需要看到这个员工从属哪些部门。
UNION
SELECT 2,'小明','1'

name nvarchar(50)
SELECT 1,'蒋大华','1,2,3'
GO
--此时已将员工表所存的IDS分别与部门相关联,下面需要将此集合中的deptName聚合成一个记录
FROM Employee AS E
--部门表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_SplitIds]'))

SELECT E.*,ISNULL(D.name,'') AS deptName
@Ids nvarchar(1000)
FROM EmployeT

)
)
;WITH EmployeT AS(
(
FROM mike2
DECLARE @i INT,@j INT,@l INT,@v VARCHAR(36);
GO

复制代码 代码如下:

name nvarchar(20),
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]'))
IF(@j = 0) set @j = @l+1;
INNER JOIN mike2 AS m2 ON m.ID=m2.id AND m.level_num=m2.level_num+1

DROP TABLE [dbo].Employee
CREATE TABLE Department
FROM mike2 AS A
deptIds varchar(1000)
DROP FUNCTION [dbo].fun_SplitIds

复制代码 代码如下:

FROM Employee AS E

复制代码 代码如下:

DROP TABLE [dbo].Department
SELECT 1,'人事部'
UNION
SELECT 3,'管理部'
,ROW_NUMBER()OVER(PARTITION BY id ORDER BY id) AS level_num
SELECT * FROM Department
SELECT 3,'管理部'
LEFT JOIN Department AS D ON DID.ID=D.id
第二步，已经得到了如上的数据，然后要做的就是根据ID分组，并对deptName列做聚合操作，但可惜的是SQL SERVER还没有提供对字符串做聚合的操作。但想到，我们处理树形结构数据时，用CTE来做关系数据，做成有树形格式的数据，如此我们也可以将这个问题转换成做树形格式的问题，代码如下：
SET @i = 0;
--创建一个表值函数，用来拆分用逗号分割的数字串，返回只有一列数字的表
,ROW_NUMBER()OVER(PARTITION BY id ORDER BY id) AS level_num
CREATE FUNCTION dbo.fun_SplitIds(
while(@j < @l)

FROM EmployeT
),mike2 AS(
id int,
),mike2 AS(
UNION ALL
FROM mike2
ORDER BY A.id OPTION (MAXRECURSION 0)

id int,
第一步，是得到如下的数据。即将员工表集合与相关的部门集合做交叉连接，其中使用了fun_SplitIds函数（作用是将ids分割成id列表），然后员工集合与这个得到的集合做交叉连接
OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID
SELECT m.id,m.name,m.deptIds,CAST(m2.deptName+','+m.deptName AS NVARCHAR(100)) AS deptName,m.level_num

(

SELECT id,MAX(level_num) AS level_num
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]'))
FROM Employee AS E
SELECT * FROM Employee
SELECT 2,'工程部'

)
部门表、员工表数据：
GO
name nvarchar(20),
),mike AS(
SELECT id,name,deptIds,CAST(deptName AS NVARCHAR(100)) AS deptName,level_num
FROM mike2 AS A
;WITH EmployeT AS(
WHERE level_num=1
SELECT 3,'小华',''
GROUP BY ID
--此时已将员工表所存的IDS分别与部门相关联,下面需要将此集合中的deptName聚合成一个记录
)
SELECT 2,'工程部'
SELECT E.*,ISNULL(D.name,'') AS deptName
GROUP BY ID
SELECT 3,'小华',''
GO
LEFT JOIN Department AS D ON DID.ID=D.id;
ORDER BY A.id OPTION (MAXRECURSION 0)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]'))

AS
CREATE TABLE Employee
END
UNION
)
初始化数据：

复制代码 代码如下:

SELECT id,name,deptIds,deptName
SELECT 1,'人事部'
GO

SET @i = @j;
SET @j = charindex(',',@Ids,@i+1);
id int,
SET @j = 0;

INNER JOIN maxMikeByIDT AS B ON A.id=B.ID AND A.level_num=B.level_num
INNER JOIN mike2 AS m2 ON m.ID=m2.id AND m.level_num=m2.level_num+1
SELECT 1,'蒋大华','1,2,3'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]'))
INSERT INTO @t_id VALUES(@v)
BEGIN

--员工的基本信息(使用OUTER APPLY将多个ID拆分开来,然后与部门表相关联)

INSERT INTO Employee(id,name,deptIds)
FROM mike
)
),maxMikeByIDT AS(
SELECT id,name,deptIds,CAST(deptName AS NVARCHAR(100)) AS deptName,level_num
SELECT 2,'小明','1'

SELECT A.id,A.name,A.deptIds,A.deptName
WHERE level_num=1

SELECT E.*,ISNULL(D.name,'') AS deptName