提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  其它

当前栏目

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

ID 多个 保存 名称 转换成 一列 逗号 隔开
2023-06-13 09:14:34 时间
背景：在做项目时，经常会遇到这样的表结构在主表的中有一列保存的是用逗号隔开ID。如，当一个员工从属多个部门时、当一个项目从属多个城市时、当一个设备从属多个项目时，很多人都会在员工表中加入一个deptIdsVARCHAR(1000)列（本文以员工从属多个部门为例），用以保存部门编号列表（很明显这不符合第一范式，但很多人这样设计了，在这篇文章中我们暂不讨论在这种应用场景下，如此设计的对与错，有兴趣的可以在回复中聊聊），然后我们在查询列表中需要看到这个员工从属哪些部门。
初始化数据：
部门表、员工表数据：
复制代码代码如下:


IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N"[dbo].[Department]"))
DROPTABLE[dbo].Department
GO
--部门表
CREATETABLEDepartment
(
idint,
namenvarchar(50)
)
INSERTINTODepartment(id,name)
SELECT1,"人事部"
UNION
SELECT2,"工程部"
UNION
SELECT3,"管理部"
SELECT*FROMDepartment

IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N"[dbo].[Employee]"))
DROPTABLE[dbo].Employee
GO
--员工表
CREATETABLEEmployee
(
idint,
namenvarchar(20),
deptIdsvarchar(1000)
)
INSERTINTOEmployee(id,name,deptIds)
SELECT1,"蒋大华","1,2,3"
UNION
SELECT2,"小明","1"
UNION
SELECT3,"小华",""
SELECT*FROMEmployee

希望得到的结果：

解决方法：

第一步，是得到如下的数据。即将员工表集合与相关的部门集合做交叉连接，其中使用了fun_SplitIds函数（作用是将ids分割成id列表），然后员工集合与这个得到的集合做交叉连接
复制代码代码如下:

SELECTE.*,ISNULL(D.name,"")ASdeptName
FROMEmployeeASE
OUTERAPPLYdbo.fun_SplitIds(E.deptIds)ASDID
LEFTJOINDepartmentASDONDID.ID=D.id;

第二步，已经得到了如上的数据，然后要做的就是根据ID分组，并对deptName列做聚合操作，但可惜的是SQLSERVER还没有提供对字符串做聚合的操作。但想到，我们处理树形结构数据时，用CTE来做关系数据，做成有树形格式的数据，如此我们也可以将这个问题转换成做树形格式的问题，代码如下：
复制代码代码如下:
;WITHEmployeTAS(
--员工的基本信息(使用OUTERAPPLY将多个ID拆分开来,然后与部门表相关联)
--此时已将员工表所存的IDS分别与部门相关联,下面需要将此集合中的deptName聚合成一个记录
SELECTE.*,ISNULL(D.name,"")ASdeptName
FROMEmployeeASE
OUTERAPPLYdbo.fun_SplitIds(E.deptIds)ASDID
LEFTJOINDepartmentASDONDID.ID=D.id
),mikeAS(
SELECTid,name,deptIds,deptName
,ROW_NUMBER()OVER(PARTITIONBYidORDERBYid)ASlevel_num
FROMEmployeT
),mike2AS(
SELECTid,name,deptIds,CAST(deptNameASNVARCHAR(100))ASdeptName,level_num
FROMmike
WHERElevel_num=1
UNIONALL
SELECTm.id,m.name,m.deptIds,CAST(m2.deptName+","+m.deptNameASNVARCHAR(100))ASdeptName,m.level_num
FROMmikeASm
INNERJOINmike2ASm2ONm.ID=m2.idANDm.level_num=m2.level_num+1
),maxMikeByIDTAS(
SELECTid,MAX(level_num)ASlevel_num
FROMmike2
GROUPBYID
)

SELECTA.id,A.name,A.deptIds,A.deptName
FROMmike2ASA
INNERJOINmaxMikeByIDTASBONA.id=B.IDANDA.level_num=B.level_num
ORDERBYA.idOPTION(MAXRECURSION0)

结果如下：

全部SQL：
复制代码代码如下:
IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N"[dbo].[Department]"))
DROPTABLE[dbo].Department
GO
--部门表
CREATETABLEDepartment
(
idint,
namenvarchar(50)
)
INSERTINTODepartment(id,name)
SELECT1,"人事部"
UNION
SELECT2,"工程部"
UNION
SELECT3,"管理部"

SELECT*FROMDepartment


IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N"[dbo].[Employee]"))
DROPTABLE[dbo].Employee
GO
--员工表
CREATETABLEEmployee
(
idint,
namenvarchar(20),
deptIdsvarchar(1000)
)
INSERTINTOEmployee(id,name,deptIds)
SELECT1,"蒋大华","1,2,3"
UNION
SELECT2,"小明","1"
UNION
SELECT3,"小华",""

SELECT*FROMEmployee

--创建一个表值函数，用来拆分用逗号分割的数字串，返回只有一列数字的表
IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N"[dbo].[fun_SplitIds]"))
DROPFUNCTION[dbo].fun_SplitIds
GO
CREATEFUNCTIONdbo.fun_SplitIds(
@Idsnvarchar(1000)
)
RETURNS@t_idTABLE(idVARCHAR(36))
AS
BEGIN
DECLARE@iINT,@jINT,@lINT,@vVARCHAR(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)asVARCHAR(36));
INSERTINTO@t_idVALUES(@v)
SET@i=@j;
END
RETURN;
END
GO


;WITHEmployeTAS(
--员工的基本信息(使用OUTERAPPLY将多个ID拆分开来,然后与部门表相关联)
--此时已将员工表所存的IDS分别与部门相关联,下面需要将此集合中的deptName聚合成一个记录
SELECTE.*,ISNULL(D.name,"")ASdeptName
FROMEmployeeASE
OUTERAPPLYdbo.fun_SplitIds(E.deptIds)ASDID
LEFTJOINDepartmentASDONDID.ID=D.id
),mikeAS(
SELECTid,name,deptIds,deptName
,ROW_NUMBER()OVER(PARTITIONBYidORDERBYid)ASlevel_num
FROMEmployeT
),mike2AS(
SELECTid,name,deptIds,CAST(deptNameASNVARCHAR(100))ASdeptName,level_num
FROMmike
WHERElevel_num=1
UNIONALL
SELECTm.id,m.name,m.deptIds,CAST(m2.deptName+","+m.deptNameASNVARCHAR(100))ASdeptName,m.level_num
FROMmikeASm
INNERJOINmike2ASm2ONm.ID=m2.idANDm.level_num=m2.level_num+1
),maxMikeByIDTAS(
SELECTid,MAX(level_num)ASlevel_num
FROMmike2
GROUPBYID
)

SELECTA.id,A.name,A.deptIds,A.deptName
FROMmike2ASA
INNERJOINmaxMikeByIDTASBONA.id=B.IDANDA.level_num=B.level_num
ORDERBYA.idOPTION(MAXRECURSION0)

相关文章