背景：在做項(xiàng)目時(shí)，經(jīng)常會(huì)遇到這樣的表結(jié)構(gòu)在主表的中有一列保存的是用逗號(hào)隔開(kāi)ID。如，當(dāng)一個(gè)員工從屬多個(gè)部門(mén)時(shí)、當(dāng)一個(gè)項(xiàng)目從屬多個(gè)城市時(shí)、當(dāng)一個(gè)設(shè)備從屬多個(gè)項(xiàng)目時(shí)，很多人都會(huì)在員工表中加入一個(gè)deptIds VARCHAR(1000)列（本文以員工從屬多個(gè)部門(mén)為例），用以保存部門(mén)編號(hào)列表（很明顯這不符合第一范式，但很多人這樣設(shè)計(jì)了，在這篇文章中我們暫不討論在這種應(yīng)用場(chǎng)景下，如此設(shè)計(jì)的對(duì)與錯(cuò)，有興趣的可以在回復(fù)中聊聊），然后我們?cè)诓樵兞斜碇行枰吹竭@個(gè)員工從屬哪些部門(mén)。
初始化數(shù)據(jù)：
部門(mén)表、員工表數(shù)據(jù)：
復(fù)制代碼 代碼如下:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]'))
DROP TABLE [dbo].Department
GO
--部門(mén)表
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
希望得到的結(jié)果：
解決方法：
第一步，是得到如下的數(shù)據(jù)。即將員工表集合與相關(guān)的部門(mén)集合做交叉連接，其中使用了fun_SplitIds函數(shù)（作用是將ids分割成id列表），然后員工集合與這個(gè)得到的集合做交叉連接
復(fù)制代碼 代碼如下:
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;
第二步，已經(jīng)得到了如上的數(shù)據(jù)，然后要做的就是根據(jù)ID分組，并對(duì)deptName列做聚合操作，但可惜的是SQL SERVER還沒(méi)有提供對(duì)字符串做聚合的操作。但想到，我們處理樹(shù)形結(jié)構(gòu)數(shù)據(jù)時(shí)，用CTE來(lái)做關(guān)系數(shù)據(jù)，做成有樹(shù)形格式的數(shù)據(jù)，如此我們也可以將這個(gè)問(wèn)題轉(zhuǎn)換成做樹(shù)形格式的問(wèn)題，代碼如下：
復(fù)制代碼 代碼如下:
;WITH EmployeT AS(
--員工的基本信息(使用OUTER APPLY將多個(gè)ID拆分開(kāi)來(lái),然后與部門(mén)表相關(guān)聯(lián))
--此時(shí)已將員工表所存的IDS分別與部門(mén)相關(guān)聯(lián),下面需要將此集合中的deptName聚合成一個(gè)記錄
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)
結(jié)果如下：
全部SQL：
復(fù)制代碼 代碼如下:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]'))
DROP TABLE [dbo].Department
GO
--部門(mén)表
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
--創(chuàng)建一個(gè)表值函數(shù)，用來(lái)拆分用逗號(hào)分割的數(shù)字串，返回只有一列數(shù)字的表
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將多個(gè)ID拆分開(kāi)來(lái),然后與部門(mén)表相關(guān)聯(lián))
--此時(shí)已將員工表所存的IDS分別與部門(mén)相關(guān)聯(lián),下面需要將此集合中的deptName聚合成一個(gè)記錄
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)
