STRING_SPLIT查询未按顺序分配序列号，新增多余行求助

尝试拆分订单项目表中以字符串分隔的序列号，目前能拆分出对应值，但序列号未按OrderItemID顺序分配至对应行，仅生成了多余的重复行，需要解决该分配问题。

现有表数据 #

OrderID OrderItemID Item    Price   SerialNo
100     101         P1      200.50  OW52288-OW52289-OW52290-OW52291-OW52292-OW52293
100     102         P1      200.50  NULL
100     103         P1      100.50  NULL
100     104         P1      300.40  NULL
100     105         P1      600.30  NULL
100     106         P1      300.50  NULL
100     107         P1      500.70  NULL
100     108         P1      200.60  NULL
100     109         P1      800.60  NULL

当前查询结果 #

OrderID OrderItemID Item    Price   value
100     101         P1     200.50   OW52288
100     101         P1     200.50   OW52289
100     101         P1     200.50   OW52290
100     101         P1     200.50   OW52291
100     101         P1     200.50   OW52292
100     101         P1     200.50   OW52293

期望结果 #

OrderID OrderItemID Item    Price   SerialNo
100     101         P1      200.5   OW52288
100     102         P1      200.5   OW52289
100     103         P1      100.5   OW52290
100     104         P1      300.4   OW52291
100     105         P1      600.3   OW52292
100     106         P1      300.5   OW52293
100     107         P1      500.7   NULL
100     108         P1      200.6   NULL
100     109         P1      800.6   NULL

表结构及现有SQL语句 #

表创建语句 #

CREATE TABLE dbo.TestOrderItemSerial(
    [OrderID] [int] NOT NULL,
    [OrderItemID] [int] NOT NULL,
    [Item] [nvarchar](50) NULL,
    [Price] [money] NOT NULL,
    [SerialNo] [nvarchar](100)
)

数据插入语句 #

Insert into dbo.TestOrderItemSerial values
(100,101,'P1',200.50,'OW52288-OW52289-OW52290-OW52291-OW52292-OW52293')
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price)values
(100,102,'P1',200.50)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,103,'P1',100.50)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,104,'P1',300.40)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,105,'P1',600.30)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,106,'P1',300.50)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,107,'P1',500.70)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,108,'P1',200.60)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,109,'P1',800.60)

现有查询语句 #

select OrderID,OrderItemID,Item,Price,value 
FROM dbo.TestOrderItemSerial with (nolock) 
CROSS APPLY String_Split(REPLACE(REPLACE((
CASE WHEN CHARINDEX('-',SerialNo) = 3 THEN REPLACE (SerialNo,'-','') 
     WHEN CHARINDEX(' ',SerialNo) = 3 THEN REPLACE (SerialNo,' ','') 
     WHEN CHARINDEX(' ',SerialNo) = 6 THEN REPLACE (SerialNo,' ','-') 
     ELSE SerialNo END)
    ,',','-'),'/','-'),'-')

解决方案 #

核心思路是给订单行按OrderItemID排序生成行号，同时给拆分后的序列号也生成对应行号，再通过订单ID和行号关联，实现序列号按顺序分配。

WITH OrderItems AS (
    SELECT 
        OrderID,
        OrderItemID,
        Item,
        Price,
        -- 按OrderItemID生成每个订单内的行号
        ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderItemID) AS RowNum
    FROM dbo.TestOrderItemSerial
),
SplitSerials AS (
    SELECT 
        OrderID,
        value AS SerialNo,
        -- 给拆分后的序列号生成行号
        ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY (SELECT NULL)) AS SerialRowNum
    FROM dbo.TestOrderItemSerial
    CROSS APPLY STRING_SPLIT(
        REPLACE(REPLACE(
            CASE 
                WHEN CHARINDEX('-', SerialNo) = 3 THEN REPLACE(SerialNo, '-', '')
                WHEN CHARINDEX(' ', SerialNo) = 3 THEN REPLACE(SerialNo, ' ', '')
                WHEN CHARINDEX(' ', SerialNo) = 6 THEN REPLACE(SerialNo, ' ', '-')
                ELSE SerialNo 
            END, ',', '-'), '/', '-'), '-'
    )
    WHERE SerialNo IS NOT NULL
)
SELECT 
    oi.OrderID,
    oi.OrderItemID,
    oi.Item,
    oi.Price,
    ss.SerialNo
FROM OrderItems oi
-- 左连接保证所有订单行都保留，无对应序列号则显示NULL
LEFT JOIN SplitSerials ss ON oi.OrderID = ss.OrderID AND oi.RowNum = ss.SerialRowNum
ORDER BY oi.OrderID, oi.OrderItemID;

说明 #

1. OrderItems CTE：为每个订单下的行按OrderItemID排序，生成唯一的行号RowNum，用于后续匹配序列号。
2. SplitSerials CTE：拆分目标行的序列号字符串，同时为每个拆分出的序列号生成行号SerialRowNum。
3. 最后通过OrderID和行号RowNum = SerialRowNum关联，将序列号按顺序分配到对应的订单行，多余的行自动显示NULL。

内容的提问来源于stack exchange，提问作者S B