资源预览内容
第1页 / 共7页
第2页 / 共7页
第3页 / 共7页
第4页 / 共7页
第5页 / 共7页
第6页 / 共7页
第7页 / 共7页
亲,该文档总共7页全部预览完了,如果喜欢就下载吧!
资源描述
在做项目时,经常会遇到这样的表结构在主表的中有一列保存的是用逗号隔开ID背景:在做项目时,经常会遇到这样的表结构在主表的中有一列保存的是用逗号隔开ID。如,当一个员工从属多个部门时、当一个项目从属多个城市时、当一个设 备从属多个项目时,很多人都会在员工表中加入一个deptIds VARCHAR(1000)列(本文以员工从属多个部门为例),用以保存部门编号列表(很明显这不符合第一范式,但很多人这样设计了,在这篇文章中我们暂 不讨论在这种应用场景下,如此设计的对与错,有兴趣的可以在回复中聊聊),然后我们在查询列表中需要看到这个员工从属哪些部门。 初始化数据: 部门表、员工表数据: 复制代码 代码如下:IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(Ndbo.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(Ndbo.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.name,m.deptIds,CAST(m2.deptName+,+m.deptName AS NVARCHAR(100) AS deptName,m.level_num FROM mike AS m INNER JOIN mike2 AS m2 ON m.ID=m2.id AND m.level_num=m2.level_num+1 ),maxMikeByIDT AS( SELECT id,MAX(level_num) AS level_num FROM mike2 GROUP BY ID ) SELECT A.id,A.name,A.deptIds,A.deptName FROM mike2 AS A INNER JOIN maxMikeByIDT AS B ON A.id=B.ID AND A.level_num=B.level_num ORDER BY A.id OPTION (MAXRECURSION 0) 结果如下:全部SQL: 复制代码 代码如下:IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(Ndbo.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(Ndbo.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 -创建一个表值函数,用来拆分用逗号分割的数字串,返回只有一列数字的表 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(Ndbo.fun_SplitIds) DROP FUNCTION dbo.fun_SplitIds GO CREATE FUNCTION dbo.fun_SplitIds( Ids nvarchar(1000) ) RETURNS t_id TABLE (id VARCHAR(36) AS BEGIN DECLARE i INT,j INT,l INT,v VARCHAR(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) as VARCHAR(36); INSERT INTO t_id VALUES(v) SET i = j; END RETURN; END GO www.iniot.cn;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.name,m.deptIds,CAST(m2.deptName+,+m.deptName AS NVARCHAR(100) AS deptName,m.level_num FROM mike AS m INNER JOIN mike2 AS m2 ON m.ID=m2.id AND m.level_num=m2.level_num+1 ),maxMikeByIDT AS( SELECT id,MAX(level_num) AS level_num FROM mike2 GROUP BY ID ) SELECT A.id,A.name,A.deptIds,A.deptName FROM mike2 AS A INNER JOIN maxMikeByIDT AS B ON A.id=B.ID AND A.level_num=B.level_num ORDER BY A.id OPTION (MAXRECURSION 0) (注:本资料素材和资料部分来自网络,仅供参考。请预览后才下载,期待您的好评与关注!)
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号