将一列包含多个ID拆分多行

最新推荐文章于 2022-12-02 08:39:24 发布

转载最新推荐文章于 2022-12-02 08:39:24 发布 · 212 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/hyshareex/p/3999467.html

文章标签：

#数据库

本文介绍如何使用SQL语句将多行数据拆分并对应到员工表中，通过创建测试表、辅助表及使用CTE（公共表表达式）进行高效操作，提供了一个灵活的方法来处理复杂数据结构。

如果下面的表结构，如何拆分多行并对应员工号呢？

首先创建测试表

CREATE TABLE Department
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    DepartmentName VARCHAR(50) NULL,
    Members NVARCHAR(500) NULL
)

CREATE TABLE Employee
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    NAME NVARCHAR(50) NULL
)
INSERT INTO Department VALUES
('行政','1,3,5,10,12'),('销售','42,23,35,40,22'),('市场','13,17,27,28,43')
SELECT * FROM dbo.Department

Employee 表的数据直接用个 Sql Generator生成一下下就好了~

--然后搞个数字辅助表作备用
CREATE TABLE #Num (ID INT)
INSERT INTO #Num
        ( ID )
SELECT TOP 500 ROW_NUMBER() OVER (ORDER BY (SELECT 1))
 FROM sys.objects a,sys.objects b

--为了方便看代码，用个CTE来写。
;WITH CTE1 AS
(SELECT a.ID,
        a.DepartmentName,
        a.Members,
        b.ID AS Pos,
        CHARINDEX(',',a.Members,b.ID) AS NextSplit
    FROM dbo.Department a
        INNER JOIN #Num b 
            ON SUBSTRING(',' + a.Members,b.ID,1) = ','
            AND b.ID <= LEN(a.Members)
),
CTE2 AS
(SELECT ID,DepartmentName,
        CASE WHEN NextSplit <> 0 THEN SUBSTRING(Members,Pos,NextSplit - Pos)
             ELSE RIGHT(Members,LEN(Members) - Pos + 1) END AS EmployeeID
    FROM CTE1)
SELECT a.* ,b.NAME
    FROM CTE2 a
        INNER JOIN dbo.Employee b ON a.EmployeeID = b.ID