SQL Server基于逗号分隔列生成派生列的逻辑问题排查

你当前的查询存在两个核心问题导致第9、10行结果不符合预期：

1. 未处理拆分后元素的前后空格：第10行FulfillmentChannelStatus列的第二个值为 Filled（逗号后带空格），你用text()="Filled"做精确匹配时无法命中，直接返回空的pos值，最终派生列结果为null。
2. 仅取第一个Filled对应的值，未按优先级筛选：不符合需求中多Filled场景下按Recruiter>Internal>Contractor返回最高优先级值的规则，比如第6行测试数据的结果也会错误返回Internal而非正确的Recruiter。

我们调整逻辑：先将两个字段按逗号拆分、去前后空格，匹配所有Filled状态对应的渠道值，再按优先级排序取最高的即可，兼容SQL Server 2016及以上版本的代码如下：

DECLARE @separator CHAR(1) = ',';

SELECT 
    t.ID,
    t.FulfillmentChannelStatus,
    t.RoleAlternateSourcingChannel,
    [Filled fulfil] = (
        SELECT TOP 1 TRIM(r.value) 
        -- 拆分FulfillmentChannelStatus，匹配所有Filled的位置
        FROM STRING_SPLIT(t.FulfillmentChannelStatus, @separator, 1) s
        -- 关联同位置的RoleAlternateSourcingChannel值
        JOIN STRING_SPLIT(t.RoleAlternateSourcingChannel, @separator, 1) r 
            ON s.ordinal = r.ordinal
        WHERE TRIM(s.value) = 'Filled'
        -- 按优先级排序，取最高的
        ORDER BY CASE TRIM(r.value)
            WHEN 'Recruiter' THEN 1
            WHEN 'Internal' THEN 2
            WHEN 'Contractor' THEN 3
            ELSE 4 END
    )
FROM ##table1 t

如果你的SQL Server版本低于2022不支持STRING_SPLIT的ordinal参数，可以用你原来的XML拆分逻辑，增加TRIM处理即可：

DECLARE @separator CHAR(1) = ','
SELECT 
    t.ID,
    t.RoleAlternateSourcingChannel,
    t.FulfillmentChannelStatus,
    [Filled fulfil] = (
        SELECT TOP 1 TRIM(channel_val)
        FROM (
            SELECT 
                pos = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
                status_val = LTRIM(RTRIM(t1.status_val.value('text()[1]', 'VARCHAR(255)'))),
                channel_val = LTRIM(RTRIM(t2.channel_val.value('text()[1]', 'VARCHAR(255)')))
            FROM (
                SELECT 
                    x = TRY_CAST('<root><r>' + REPLACE(t.FulfillmentChannelStatus, @separator, '</r><r>') + '</r></root>' AS XML)
            ) xml1
            CROSS APPLY xml1.x.nodes('/root/r') t1(status_val)
            CROSS APPLY (
                SELECT x = TRY_CAST('<root><r>' + REPLACE(t.RoleAlternateSourcingChannel, @separator, '</r><r>') + '</r></root>' AS XML)
            ) xml2
            CROSS APPLY xml2.x.nodes('/root/r[position() = sql:column("pos")]') t2(channel_val)
            WHERE LTRIM(RTRIM(t1.status_val.value('text()[1]', 'VARCHAR(255)'))) = 'Filled'
        ) matched
        ORDER BY CASE TRIM(channel_val)
            WHEN 'Recruiter' THEN 1
            WHEN 'Internal' THEN 2
            WHEN 'Contractor' THEN 3
            ELSE 4 END
    )
FROM ##table1 t

运行修复后的查询，第9行返回Recruiter、第10行返回Recruiter，完全符合需求。

内容的提问来源于stack exchange，提问作者Hawfinch Taylor