一列保存多个ID(将多个用逗号隔的ID转换成用逗号隔开的名称).docxVIP

在做项目时，经常会遇到这样的表结构在主表的中有一列保存的是用逗号隔开ID 背景：在做项目时，经常会遇到这样的表结构在主表的中有一列保存的是用逗号隔开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.deptIds,CAST(m2.deptName+,+m.deptName AS NVARCHAR(100)) AS deptName,m.level_num FROM mike A