資源描述：
《一列保存多個id(將多個用逗號隔開的id轉(zhuǎn)換成用逗號隔開的名稱)》由會員上傳分享，免費在線閱讀，更多相關內(nèi)容在應用文檔-天天文庫。
1、在做項目時，經(jīng)常會遇到這樣的表結(jié)構(gòu)在主表的中有一列保存的是用逗號隔開ID背景：在做項目時，經(jīng)常會遇到這樣的表結(jié)構(gòu)在主表的中有一列保存的是用逗號隔開ID。如，當一個員工從屬多個部門時、當一個項目從屬多個城市時、當一個設備從屬多個項目時，很多人都會在員工表中加入一個deptIdsVARCHAR(1000)列（本文以員工從屬多個部門為例），用以保存部門編號列表（很明顯這不符合第一范式，但很多人這樣設計了，在這篇文章中我們暫不討論在這種應用場景下，如此設計的對與錯，有興趣的可以在回復中聊聊），然后我們在查
2、詢列表中需要看到這個員工從屬哪些部門。初始化數(shù)據(jù)：部門表、員工表數(shù)據(jù)：復制代碼代碼如下:IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[Department]'))DROPTABLE[dbo].DepartmentGO--部門表CREATETABLEDepartment(idint,namenvarchar(50))INSERTINTODepartment(id,name)SELECT1,'人事部'UNIONSELE
3、CT2,'工程部'UNIONSELECT3,'管理部'SELECT*FROMDepartmentIFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[Employee]'))DROPTABLE[dbo].EmployeeGO--員工表CREATETABLEEmployee(idint,namenvarchar(20),deptIdsvarchar(1000))INSERTINTOEmployee(id,name,deptI
4、ds)SELECT1,'蔣大華','1,2,3'UNIONSELECT2,'小明','1'UNIONSELECT3,'小華',''SELECT*FROMEmployee希望得到的結(jié)果：解決方法：第一步，是得到如下的數(shù)據(jù)。即將員工表集合與相關的部門集合做交叉連接，其中使用了fun_SplitIds函數(shù)（作用是將ids分割成id列表），然后員工集合與這個得到的集合做交叉連接復制代碼代碼如下:SELECTE.*,ISNULL(D.name,'')ASdeptNameFROMEmployeeASEOUTE
5、RAPPLYdbo.fun_SplitIds(E.deptIds)ASDIDLEFTJOINDepartmentASDONDID.ID=D.id;第二步，已經(jīng)得到了如上的數(shù)據(jù)，然后要做的就是根據(jù)ID分組，并對deptName列做聚合操作，但可惜的是SQLSERVER還沒有提供對字符串做聚合的操作。但想到，我們處理樹形結(jié)構(gòu)數(shù)據(jù)時，用CTE來做關系數(shù)據(jù)，做成有樹形格式的數(shù)據(jù)，如此我們也可以將這個問題轉(zhuǎn)換成做樹形格式的問題，代碼如下：復制代碼代碼如下:;WITHEmployeTAS(--員工的基本信息(
6、使用OUTERAPPLY將多個ID拆分開來,然后與部門表相關聯(lián))--此時已將員工表所存的IDS分別與部門相關聯(lián),下面需要將此集合中的deptName聚合成一個記錄SELECTE.*,ISNULL(D.name,'')ASdeptNameFROMEmployeeASEOUTERAPPLYdbo.fun_SplitIds(E.deptIds)ASDIDLEFTJOINDepartmentASDONDID.ID=D.id),mikeAS(SELECTid,name,deptIds,deptName,RO
7、W_NUMBER()OVER(PARTITIONBYidORDERBYid)ASlevel_numFROMEmployeT),mike2AS(SELECTid,name,deptIds,CAST(deptNameASNVARCHAR(100))ASdeptName,level_numFROMmikeWHERElevel_num=1UNIONALLSELECTm.id,m.name,m.deptIds,CAST(m2.deptName+','+m.deptNameASNVARCHAR(100))AS
8、deptName,m.level_numFROMmikeASmINNERJOINmike2ASm2ONm.ID=m2.idANDm.level_num=m2.level_num+1),maxMikeByIDTAS(SELECTid,MAX(level_num)ASlevel_numFROMmike2GROUPBYID)SELECTA.id,A.name,A.deptIds,A.deptNameFROMmike2ASAINNERJOINmaxMikeByIDTASBONA.id=B.
