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

我会带着你远行 2022-11-21 15:19 220阅读 0赞

如下图salers和percents是一一对应的关系，现需要将salers和percents进行拆开。结果如下图2  
![在这里插入图片描述][20201102163312586.png_pic_center]  
![在这里插入图片描述][20201102163420735.png_pic_center]

# 创建模拟数据表 #

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70_pic_center]  
看起来执行效率也可以。

[参考文档][Link 1]

[20201102163312586.png_pic_center]: /images/20221120/979ecf6edef1439aaee62178a7761fa6.png
[20201102163420735.png_pic_center]: /images/20221120/0cc19d69061e47629c60ac7e143acfdf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/3b509da335ea4358b95473edc5109a8e.png
[Link 1]: https://docs.%20microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15