如何用SELECT语句将运营天数数字串转为星期缩写字符串？

针对你需要将Operation_days字段（如134、12367这类数字组合）转换为对应星期缩写的需求，核心思路是拆分每个数字字符→映射为星期缩写→聚合拼接，无需硬编码所有组合，以下是主流数据库的实现方案：

SQL Server 版本 #

方案1：适用于SQL Server 2022+（支持空分隔符的STRING_SPLIT） #

SELECT 
    Operation_days,
    STRING_AGG(CASE c.value
        WHEN '1' THEN 'M'
        WHEN '2' THEN 'T'
        WHEN '3' THEN 'W'
        WHEN '4' THEN 'T'
        WHEN '5' THEN 'F'
        WHEN '6' THEN 'S'
        WHEN '7' THEN 'S'
    END, ',') AS Days
FROM YourTable
CROSS APPLY STRING_SPLIT(Operation_days, '') c
GROUP BY Operation_days;

方案2：适用于SQL Server 2016-2019（用递归CTE拆分字符） #

WITH SplitCTE AS (
    SELECT 
        Operation_days,
        CAST(LEFT(Operation_days, 1) AS VARCHAR(1)) AS DayChar,
        STUFF(Operation_days, 1, 1, '') AS RemainingChars
    FROM YourTable
    WHERE Operation_days IS NOT NULL AND Operation_days != ''
    UNION ALL
    SELECT 
        Operation_days,
        CAST(LEFT(RemainingChars, 1) AS VARCHAR(1)) AS DayChar,
        STUFF(RemainingChars, 1, 1, '') AS RemainingChars
    FROM SplitCTE
    WHERE RemainingChars != ''
)
SELECT 
    Operation_days,
    STRING_AGG(CASE DayChar
        WHEN '1' THEN 'M'
        WHEN '2' THEN 'T'
        WHEN '3' THEN 'W'
        WHEN '4' THEN 'T'
        WHEN '5' THEN 'F'
        WHEN '6' THEN 'S'
        WHEN '7' THEN 'S'
    END, ',') AS Days
FROM SplitCTE
GROUP BY Operation_days
OPTION (MAXRECURSION 0);

MySQL 版本 #

利用数字序列表拆分字符，再通过GROUP_CONCAT聚合：

SELECT 
    t.Operation_days,
    GROUP_CONCAT(
        CASE SUBSTRING(t.Operation_days, n.num, 1)
            WHEN '1' THEN 'M'
            WHEN '2' THEN 'T'
            WHEN '3' THEN 'W'
            WHEN '4' THEN 'T'
            WHEN '5' THEN 'F'
            WHEN '6' THEN 'S'
            WHEN '7' THEN 'S'
        END
        ORDER BY n.num SEPARATOR ','
    ) AS Days
FROM YourTable t
JOIN (
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
) n ON n.num <= LENGTH(t.Operation_days)
GROUP BY t.Operation_days;

PostgreSQL 版本 #

方案1：用STRING_TO_ARRAY+UNNEST拆分 #

SELECT 
    Operation_days,
    STRING_AGG(
        CASE unnest_day
            WHEN '1' THEN 'M'
            WHEN '2' THEN 'T'
            WHEN '3' THEN 'W'
            WHEN '4' THEN 'T'
            WHEN '5' THEN 'F'
            WHEN '6' THEN 'S'
            WHEN '7' THEN 'S'
        END, ','
    ) AS Days
FROM YourTable,
     UNNEST(STRING_TO_ARRAY(Operation_days, NULL)) AS unnest_day
GROUP BY Operation_days;

方案2：用REGEXP_SPLIT_TO_TABLE拆分 #

SELECT 
    Operation_days,
    STRING_AGG(
        CASE split_part
            WHEN '1' THEN 'M'
            WHEN '2' THEN 'T'
            WHEN '3' THEN 'W'
            WHEN '4' THEN 'T'
            WHEN '5' THEN 'F'
            WHEN '6' THEN 'S'
            WHEN '7' THEN 'S'
        END, ','
    ) AS Days
FROM YourTable,
     REGEXP_SPLIT_TO_TABLE(Operation_days, '') AS split_part
GROUP BY Operation_days;

核心逻辑说明 #

所有方案都遵循三步逻辑：

1. 拆分：将Operation_days的每个数字字符拆分为独立行
2. 映射：通过CASE语句将数字（1-7）转换为对应的星期缩写（M/T/W/T/F/S/S）
3. 聚合：使用字符串聚合函数（STRING_AGG/GROUP_CONCAT）将缩写用逗号拼接成最终字符串

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