数据库逗号分隔数据解析为License#与Item#一对多列的问题

问题说明 #

数据库中存储着以特殊格式逗号分隔的License和Item数据，需要解析为License与对应Item组一一对应的结果，但现有SQL执行结果不符合预期：

当前执行结果 #

• 1234 对应 4555,8777,4444,4415,4444
• 8854 对应 4555,8777,4444,4415,4444
• 6987 对应 4555,8777,4444,4415,4444

预期结果 #

• 1234 对应 4555,8777,4444
• 8854 对应 4415
• 6987 对应 4444

现有SQL代码 #

DROP TABLE IF EXISTS testdata;
CREATE TABLE testdata (
    LicenseNumberList VARCHAR(100),
    ItemNumberList VARCHAR(100)
);

-- Insert data
INSERT INTO testdata (LicenseNumberList, ItemNumberList)
VALUES ('[1234],[8854],[6987]', '[4555,8777,4444],[4415],[4444]');

DROP FUNCTION IF EXISTS dbo.SplitString
GO
-- Create a split function
CREATE FUNCTION dbo.SplitString
(
    @String VARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Result TABLE (Value VARCHAR(MAX))
AS
BEGIN
    DECLARE @Value VARCHAR(MAX)
    
    WHILE CHARINDEX(@Delimiter, @String) > 0
    BEGIN
        SET @Value = SUBSTRING(@String, 1, CHARINDEX(@Delimiter, @String) - 1)
        INSERT INTO @Result (Value) VALUES (@Value)
        SET @String = SUBSTRING(@String, CHARINDEX(@Delimiter, @String) + 1, LEN(@String))
    END
    
    IF LEN(@String) > 0
        INSERT INTO @Result (Value) VALUES (@String)
    
    RETURN
END;
GO

-- Split the license numbers and item numbers into separate rows
WITH LicenseNumbersParsed AS (
    SELECT
        LTRIM(RTRIM(REPLACE(REPLACE(Value, '[', ''), ']', ''))) AS LicenseNumber,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
    FROM
        testdata
    CROSS APPLY dbo.SplitString(LicenseNumberList, ',')
), ItemNumbersParsed AS (
    SELECT
        ln.RowNumber,
        LTRIM(RTRIM(REPLACE(REPLACE(Value, '[', ''), ']', ''))) AS ItemNumber,
        ROW_NUMBER() OVER (PARTITION BY ln.RowNumber ORDER BY (SELECT NULL)) AS ItemRowNumber
    FROM
        testdata
    CROSS APPLY dbo.SplitString(ItemNumberList, ',')
    JOIN LicenseNumbersParsed ln ON 1 = 1
)
SELECT
    ln.LicenseNumber,
    STRING_AGG(ip.ItemNumber, ',') WITHIN GROUP (ORDER BY ip.ItemRowNumber) AS ItemNumberList
FROM
    LicenseNumbersParsed ln
JOIN ItemNumbersParsed ip ON ln.RowNumber = ip.RowNumber
GROUP BY
    ln.LicenseNumber
ORDER BY
    ln.LicenseNumber;

问题根源 #

1. 分割符错误：用单个逗号分割ItemNumberList时，会把Item组内部的逗号（如[4555,8777,4444]里的逗号）和外层分组的逗号（],[中的逗号）混淆，导致分割出错误的元素。
2. 笛卡尔积关联：ItemNumbersParsed中用JOIN LicenseNumbersParsed ln ON 1 = 1做了全量关联，使得每个Item元素都和所有License绑定，最终聚合后把所有Item拼到了每个License下。

修正方案 #

核心思路 #

先按外层分组分隔符],[分割，得到License和Item组的一一对应关系，再清理每个分组的首尾括号，最终关联输出。

修正后的SQL代码 #

DROP TABLE IF EXISTS testdata;
CREATE TABLE testdata (
    LicenseNumberList VARCHAR(100),
    ItemNumberList VARCHAR(100)
);

-- Insert data
INSERT INTO testdata (LicenseNumberList, ItemNumberList)
VALUES ('[1234],[8854],[6987]', '[4555,8777,4444],[4415],[4444]');

DROP FUNCTION IF EXISTS dbo.SplitString
GO
-- 升级分割函数：支持多字符分隔符，同时保留行号保证顺序
CREATE FUNCTION dbo.SplitString
(
    @String VARCHAR(MAX),
    @Delimiter VARCHAR(10)
)
RETURNS @Result TABLE (Value VARCHAR(MAX), RowNum INT IDENTITY(1,1))
AS
BEGIN
    DECLARE @Value VARCHAR(MAX)
    
    WHILE CHARINDEX(@Delimiter, @String) > 0
    BEGIN
        SET @Value = SUBSTRING(@String, 1, CHARINDEX(@Delimiter, @String) - 1)
        INSERT INTO @Result (Value) VALUES (@Value)
        SET @String = SUBSTRING(@String, CHARINDEX(@Delimiter, @String) + LEN(@Delimiter), LEN(@String))
    END
    
    IF LEN(@String) > 0
        INSERT INTO @Result (Value) VALUES (@String)
    
    RETURN
END;
GO

-- 解析License和对应Item组
WITH LicenseGroups AS (
    SELECT
        -- 清理每个分组的首尾括号
        LTRIM(RTRIM(REPLACE(REPLACE(Value, '[', ''), ']', ''))) AS LicenseNumber,
        RowNum
    FROM
        testdata
    -- 按外层分组分隔符"],["分割
    CROSS APPLY dbo.SplitString(LicenseNumberList, '],[')
), ItemGroups AS (
    SELECT
        LTRIM(RTRIM(REPLACE(REPLACE(Value, '[', ''), ']', ''))) AS ItemNumberList,
        RowNum
    FROM
        testdata
    CROSS APPLY dbo.SplitString(ItemNumberList, '],[')
)
SELECT
    lg.LicenseNumber,
    ig.ItemNumberList
FROM
    LicenseGroups lg
-- 按行号关联，保证License和Item组一一对应
JOIN ItemGroups ig ON lg.RowNum = ig.RowNum
ORDER BY
    lg.LicenseNumber;

扩展：拆分为单个Item的一对多行 #

如果需要将Item组拆分为单个Item行（每个License对应多个Item行），可以在上述基础上再嵌套一次分割：

-- 拆分为单个Item的一对多结果
WITH LicenseGroups AS (
    SELECT
        LTRIM(RTRIM(REPLACE(REPLACE(Value, '[', ''), ']', ''))) AS LicenseNumber,
        RowNum
    FROM
        testdata
    CROSS APPLY dbo.SplitString(LicenseNumberList, '],[')
), ItemGroups AS (
    SELECT
        LTRIM(RTRIM(REPLACE(REPLACE(Value, '[', ''), ']', ''))) AS ItemNumbers,
        RowNum
    FROM
        testdata
    CROSS APPLY dbo.SplitString(ItemNumberList, '],[')
), ItemNumbers AS (
    SELECT
        lg.LicenseNumber,
        LTRIM(RTRIM(s.Value)) AS ItemNumber
    FROM
        LicenseGroups lg
    JOIN ItemGroups ig ON lg.RowNum = ig.RowNum
    -- 分割Item组内部的逗号
    CROSS APPLY dbo.SplitString(ig.ItemNumbers, ',') s
)
SELECT * FROM ItemNumbers ORDER BY LicenseNumber;

内容的提问来源于stack exchange，提问作者Michael Thomas