如何将T-SQL中三个字符串格式数值列表按顺序加载到表变量？

方法一：适用于SQL Server 2022及以上（或Azure SQL） #

利用STRING_SPLIT函数的ordinal参数直接获取拆分后的顺序，通过行号关联三个列表的对应值插入表变量：

DECLARE @article_list VARCHAR(MAX) = '1060226, 20653, 39986, 1041443, 1060303, 1057353, 1050423, 1057354, 1059541, 1050570';
DECLARE @store_list VARCHAR(MAX) = '1046, 1046, 1046, 1046, 1107, 1107, 1107, 1107, 1107, 1107';
DECLARE @order_list VARCHAR(MAX) = '1012406, 2008322, 2008322, 2008322, 2011092, 1020199, 1020199, 1020199, 1020176, 2010731';

DECLARE @rows_to_delete TABLE (rn INT IDENTITY(1,1), art INT, ord INT, stor INT);

INSERT INTO @rows_to_delete (art, ord, stor)
SELECT 
    CAST(a.value AS INT) AS art,
    CAST(o.value AS INT) AS ord,
    CAST(s.value AS INT) AS stor
FROM STRING_SPLIT(@article_list, ',', 1) a
JOIN STRING_SPLIT(@store_list, ',', 1) s ON a.ordinal = s.ordinal
JOIN STRING_SPLIT(@order_list, ',', 1) o ON a.ordinal = o.ordinal;

-- 验证插入结果
SELECT * FROM @rows_to_delete;

方法二：适用于SQL Server 2016及以下版本 #

通过XML拆分字符串并生成行号，再关联三个列表的对应值插入：

DECLARE @article_list VARCHAR(MAX) = '1060226, 20653, 39986, 1041443, 1060303, 1057353, 1050423, 1057354, 1059541, 1050570';
DECLARE @store_list VARCHAR(MAX) = '1046, 1046, 1046, 1046, 1107, 1107, 1107, 1107, 1107, 1107';
DECLARE @order_list VARCHAR(MAX) = '1012406, 2008322, 2008322, 2008322, 2011092, 1020199, 1020199, 1020199, 1020176, 2010731';

DECLARE @rows_to_delete TABLE (rn INT IDENTITY(1,1), art INT, ord INT, stor INT);

-- 拆分article_list并生成行号
WITH ArticleCTE AS (
    SELECT 
        CAST(Split.a.value('.', 'VARCHAR(100)') AS INT) AS art,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
    FROM (SELECT CAST('<M>' + REPLACE(@article_list, ', ', '</M><M>') + '</M>' AS XML) AS Data) AS A
    CROSS APPLY Data.nodes('/M') AS Split(a)
),
-- 拆分store_list并生成行号
StoreCTE AS (
    SELECT 
        CAST(Split.a.value('.', 'VARCHAR(100)') AS INT) AS stor,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
    FROM (SELECT CAST('<M>' + REPLACE(@store_list, ', ', '</M><M>') + '</M>' AS XML) AS Data) AS S
    CROSS APPLY Data.nodes('/M') AS Split(a)
),
-- 拆分order_list并生成行号
OrderCTE AS (
    SELECT 
        CAST(Split.a.value('.', 'VARCHAR(100)') AS INT) AS ord,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
    FROM (SELECT CAST('<M>' + REPLACE(@order_list, ', ', '</M><M>') + '</M>' AS XML) AS Data) AS O
    CROSS APPLY Data.nodes('/M') AS Split(a)
)
-- 关联插入表变量
INSERT INTO @rows_to_delete (art, ord, stor)
SELECT a.art, o.ord, s.stor
FROM ArticleCTE a
JOIN StoreCTE s ON a.rn = s.rn
JOIN OrderCTE o ON a.rn = o.rn;

-- 验证插入结果
SELECT * FROM @rows_to_delete;

注意事项 #

• 两种方法的核心是拆分字符串时保留原始顺序，再通过行号关联三个列表的对应值。
• 方法一中STRING_SPLIT的第三个参数1用于启用返回拆分元素的顺序位置（ordinal），确保对应关系准确。
• 方法二中需注意REPLACE的分隔符要和原字符串一致（这里是, ，逗号加空格），如果原字符串仅用逗号分隔，需修改为REPLACE(@article_list, ',', '</M><M>')。

内容的提问来源于stack exchange，提问作者Mauro Ghiglia