SQL Server无需硬编码将deliverDay数字串转为对应星期名的实现方法

核心思路 #

将分号分隔的数字串拆分为单行单个数字，和预设的数字-星期映射关系关联后，再按原顺序拼接为分号分隔的名称串即可，支持任意数字组合的转换。

方案1：SQL Server 2016+ 最简方案（推荐） #

SQL Server 2016开始支持STRING_SPLIT拆分函数，2022及以上版本新增ordinal参数可保留拆分后的原始顺序，搭配STRING_AGG拼接函数即可快速实现需求：

SELECT 
    t.deliverDay,
    -- 按原始顺序拼接星期名称，2022以下版本可去掉 WITHIN GROUP 部分
    STRING_AGG(w.week_name, ';') WITHIN GROUP (ORDER BY s.ordinal) AS converted_deliverDay
FROM 你的业务表名 t
-- 拆分分号分隔的数字串，第三个参数1表示返回顺序号，仅2022+支持
CROSS APPLY STRING_SPLIT(t.deliverDay, ';', 1) s
-- 数字与星期的映射关系，可按需调整
INNER JOIN (
    VALUES 
        ('0', 'Monday'),
        ('1', 'Tuesday'),
        ('2', 'Wednesday'),
        ('3', 'Thursday'),
        ('4', 'Friday'),
        ('5', 'Saturday'),
        ('6', 'Sunday')
) w(week_num, week_name) ON s.value = w.week_num
GROUP BY t.deliverDay -- 需保留的其他业务列也写在GROUP BY中

方案2：SQL Server 2008及以上兼容方案 #

低版本SQL Server没有内置拆分、拼接函数，可通过XML实现相同逻辑，且默认保留原始顺序：

SELECT 
    t.deliverDay,
    -- 去掉拼接后开头多余的分号
    STUFF((
        SELECT ';' + w.week_name
        FROM (
            -- 用XML拆分分号分隔的字符串，同时生成顺序号
            SELECT 
                LTRIM(RTRIM(m.n.value('.[1]','varchar(10)'))) AS week_num,
                ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
            FROM (SELECT CAST('<XMLRoot><RowData>' + REPLACE(t.deliverDay,';','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x) tmp
            CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
        ) s
        INNER JOIN (
            VALUES 
                ('0', 'Monday'),
                ('1', 'Tuesday'),
                ('2', 'Wednesday'),
                ('3', 'Thursday'),
                ('4', 'Friday'),
                ('5', 'Saturday'),
                ('6', 'Sunday')
        ) w(week_num, week_name) ON s.week_num = w.week_num
        ORDER BY s.rn
        FOR XML PATH(''), TYPE
    ).value('.[1]','varchar(100)'), 1, 1, '') AS converted_deliverDay
FROM 你的业务表名 t

补充说明 #

• 映射关系可灵活调整，后续需要修改星期名、新增/删除映射值，仅需修改VALUES中的对应项即可，无需调整拆分、拼接逻辑
• 若需兼容非法值：deliverDay中存在不在0-6范围内的数值时，可将INNER JOIN改为LEFT JOIN，配合COALESCE(w.week_name, '未知')处理非法值
• 若该逻辑使用频率高，可将数字-星期的映射关系单独存储为物理维度表，复用性更强

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