一列保留多個ID(將多個用逗號離隔的ID轉換成用逗號離隔的稱號)。本站提示廣大學習愛好者：（一列保留多個ID(將多個用逗號離隔的ID轉換成用逗號離隔的稱號)）文章只能為提供參考，不一定能成為您想要的結果。以下是一列保留多個ID(將多個用逗號離隔的ID轉換成用逗號離隔的稱號)正文
配景：在做項目時，常常會碰到如許的表構造在主表的中有一列保留的是用逗號離隔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)
