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

来源:asp之家 时间:2012-08-21 10:37:37 

背景:在做项目时,经常会遇到这样的表结构在主表的中有一列保存的是用逗号隔开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.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(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

--创建一个表值函数,用来拆分用逗号分割的数字串,返回只有一列数字的表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[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


;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)

标签:一列,多个ID
0
投稿

猜你喜欢

  • 使用python进行nc转tif的3种情况解决

    2022-05-09 11:22:43
  • python实现图像边缘检测

    2022-03-17 15:35:11
  • 使table也能overflow:hidden

    2008-08-18 21:04:00
  • 关于python的bottle框架跨域请求报错问题的处理方法

    2021-12-06 23:00:11
  • 服务端XMLHTTP(ServerXMLHTTP in ASP)进阶应用-User Agent伪装

    2008-11-11 12:29:00
  • Python绘图模块 turtle案例代码

    2022-12-16 01:28:10
  • python 遍历磁盘目录的三种方法

    2021-10-15 06:36:14
  • mysql中找不到my.ini文件的问题及解决

    2024-01-17 13:56:18
  • 解决pandas中读取中文名称的csv文件报错的问题

    2021-08-07 00:53:40
  • Python中字符串类型代码的执行函数——eval()、exec()和compile()详解

    2022-07-08 09:14:12
  • 详解使用 pyenv 管理多个版本 python 环境

    2023-11-01 03:40:05
  • PyTorch中可视化工具的使用

    2021-03-05 23:11:39
  • 浅谈tensorflow1.0 池化层(pooling)和全连接层(dense)

    2021-11-09 05:06:17
  • 完美解决python3.7 pip升级 拒绝访问问题

    2023-08-10 07:45:33
  • sql server中批量插入与更新两种解决方案分享(存储过程)

    2012-05-22 18:29:59
  • python+matplotlib绘制3D条形图实例代码

    2022-05-03 16:36:56
  • 将mysql转换到oracle必须了解的50件事

    2024-01-17 22:08:42
  • oracle 存储过程和函数例子

    2009-08-08 22:27:00
  • 聊聊Python中关于a=[[]]*3的反思

    2021-09-08 05:12:46
  • Python中条件语句、循环语句和pass语句的使用示例

    2021-05-13 23:23:27
  • asp之家 网络编程 m.aspxhome.com