资源描述：
《一列保存多个id(将多个用逗号隔开的id转换成用逗号隔开的名称)》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、在做项目时，经常会遇到这样的表结构在主表的中有一列保存的是用逗号隔开ID背景：在做项目时，经常会遇到这样的表结构在主表的中有一列保存的是用逗号隔开ID。如，当一个员工从属多个部门时、当一个项目从属多个城市时、当一个设备从属多个项目时，很多人都会在员工表中加入一个deptIdsVARCHAR(1000)列（本文以员工从属多个部门为例），用以保存部门编号列表（很明显这不符合第一范式，但很多人这样设计了，在这篇文章中我们暂不讨论在这种应用场景下，如此设计的对与错，有兴趣的可以在回复中聊聊），然后我们在查询列表中需要看到这个员工从属哪些部门。初始
2、化数据：部门表、员工表数据：复制代码代码如下:IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[Department]'))DROPTABLE[dbo].DepartmentGO--部门表CREATETABLEDepartment(idint,namenvarchar(50))INSERTINTODepartment(id,name)SELECT1,'人事部'UNIONSELECT2,'工程部'UNIONSELECT3,'管理部'SELECT*FROMDepa
3、rtmentIFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[Employee]'))DROPTABLE[dbo].EmployeeGO--员工表CREATETABLEEmployee(idint,namenvarchar(20),deptIdsvarchar(1000))INSERTINTOEmployee(id,name,deptIds)SELECT1,'蒋大华','1,2,3'UNIONSELECT2,'小明','1'UNIONSELECT3,'小华',
4、''SELECT*FROMEmployee希望得到的结果：解决方法：第一步，是得到如下的数据。即将员工表集合与相关的部门集合做交叉连接，其中使用了fun_SplitIds函数（作用是将ids分割成id列表），然后员工集合与这个得到的集合做交叉连接复制代码代码如下:SELECTE.*,ISNULL(D.name,'')ASdeptNameFROMEmployeeASEOUTERAPPLYdbo.fun_SplitIds(E.deptIds)ASDIDLEFTJOINDepartmentASDONDID.ID=D.id;第二步，已经得到了如上
5、的数据，然后要做的就是根据ID分组，并对deptName列做聚合操作，但可惜的是SQLSERVER还没有提供对字符串做聚合的操作。但想到，我们处理树形结构数据时，用CTE来做关系数据，做成有树形格式的数据，如此我们也可以将这个问题转换成做树形格式的问题，代码如下：复制代码代码如下:;WITHEmployeTAS(--员工的基本信息(使用OUTERAPPLY将多个ID拆分开来,然后与部门表相关联)--此时已将员工表所存的IDS分别与部门相关联,下面需要将此集合中的deptName聚合成一个记录SELECTE.*,ISNULL(D.name,
6、'')ASdeptNameFROMEmployeeASEOUTERAPPLYdbo.fun_SplitIds(E.deptIds)ASDIDLEFTJOINDepartmentASDONDID.ID=D.id),mikeAS(SELECTid,name,deptIds,deptName,ROW_NUMBER()OVER(PARTITIONBYidORDERBYid)ASlevel_numFROMEmployeT),mike2AS(SELECTid,name,deptIds,CAST(deptNameASNVARCHAR(100))ASde
7、ptName,level_numFROMmikeWHERElevel_num=1UNIONALLSELECTm.id,m.name,m.deptIds,CAST(m2.deptName+','+m.deptNameASNVARCHAR(100))ASdeptName,m.level_numFROMmikeASmINNERJOINmike2ASm2ONm.ID=m2.idANDm.level_num=m2.level_num+1),maxMikeByIDTAS(SELECTid,MAX(level_num)ASlevel_numFROMmi
8、ke2GROUPBYID)SELECTA.id,A.name,A.deptIds,A.deptNameFROMmike2ASAINNERJOINmaxMikeByIDTASBONA.id=B.
