SQL Server中利用substring_split进行数据按指定分隔符展开

如下图salers和percents是一一对应的关系，现需要将salers和percents进行拆开。结果如下图2

创建模拟数据表

create table sales_test(
order_id int,
sku_id int,
salers nvarchar(50),
percents nvarchar(50)
)

通过循环创建模拟数据

DECLARE @i INT = 1;
WHILE @i <= 100000
BEGIN
   insert into sales_test values (@i,@i,'a,b,c','0.4,0.25,0.35')
   SET @i = @i+1;
END;

执行运算进行拆分

with T1 AS (
SELECT order_id, sku_id,percents,salers, value AS saler
,row_number() over(partition by order_id,sku_id order by percents) as rn
FROM dm.sales_test  A
    CROSS APPLY STRING_SPLIT(salers, ',')
		)
,T2 AS (		
SELECT order_id, sku_id,value AS [percent]
,row_number() over(partition by order_id,sku_id order by percents) as rn
FROM dm.SALES_TEST  A
    CROSS APPLY STRING_SPLIT(percents, ',')
		)
SELECT T1.order_id
,T1.sku_id
,T1.salers
,T1.percents
,T1.saler
,T2.[percent]
FROM T1
JOIN T2
ON T1.order_id = T2.order_id
AND T1.sku_id = T2.sku_id
AND T1.rn = T2.rn

看起来执行效率也可以。

参考文档