SQL拼接日期字符串求助：CASE表达式无法实现拼接，求查询方案

我明白你遇到的问题了——用CASE表达式直接赋值变量确实行不通，因为CASE是返回单个值的表达式，不是用来做变量赋值的。下面我分两种场景给你写对应的SQL解决方案：

场景1：处理独立的布尔星期字段（EveryDay、SUN、MON等） #

如果你的表是用单独的布尔字段（比如EveryDay、SUN、MON）标记是否选中对应星期，我们可以用CASE生成每个星期的名称，再通过聚合拼接函数把非空的结果组合起来，同时优先处理EveryDay=1的特殊情况。

SQL Server 示例代码 #

SELECT
    -- 优先判断EveryDay，选中则直接返回"Every Day"
    CASE WHEN EveryDay = 1 THEN 'Every Day'
         ELSE
             -- 拼接所有选中的星期名称，按星期顺序排序
             STRING_AGG(
                 CASE
                     WHEN SUN = 1 THEN 'SUNDAY'
                     WHEN MON = 1 THEN 'MONDAY'
                     WHEN TUE = 1 THEN 'TUESDAY'
                     WHEN WED = 1 THEN 'WEDNESDAY'
                     WHEN THU = 1 THEN 'THURSDAY'
                     WHEN FRI = 1 THEN 'FRIDAY'
                     WHEN SAT = 1 THEN 'SATURDAY'
                 END, ', '
             ) WITHIN GROUP (ORDER BY 
                 CASE
                     WHEN SUN = 1 THEN 1
                     WHEN MON = 1 THEN 2
                     WHEN TUE = 1 THEN 3
                     WHEN WED = 1 THEN 4
                     WHEN THU = 1 THEN 5
                     WHEN FRI = 1 THEN 6
                     WHEN SAT = 1 THEN 7
                 END
             )
         END AS Days
FROM YourTableName;

MySQL 示例代码 #

MySQL用GROUP_CONCAT替代STRING_AGG，写法如下：

SELECT
    CASE WHEN EveryDay = 1 THEN 'Every Day'
         ELSE
             GROUP_CONCAT(
                 CASE
                     WHEN SUN = 1 THEN 'SUNDAY'
                     WHEN MON = 1 THEN 'MONDAY'
                     WHEN TUE = 1 THEN 'TUESDAY'
                     WHEN WED = 1 THEN 'WEDNESDAY'
                     WHEN THU = 1 THEN 'THURSDAY'
                     WHEN FRI = 1 THEN 'FRIDAY'
                     WHEN SAT = 1 THEN 'SATURDAY'
                 END
                 ORDER BY 
                     CASE
                         WHEN SUN = 1 THEN 1
                         WHEN MON = 1 THEN 2
                         WHEN TUE = 1 THEN 3
                         WHEN WED = 1 THEN 4
                         WHEN THU = 1 THEN 5
                         WHEN FRI = 1 THEN 6
                         WHEN SAT = 1 THEN 7
                     END
                 SEPARATOR ', '
             )
         END AS Days
FROM YourTableName
-- 如果是批量处理多条数据，需要按主键分组
GROUP BY YourPrimaryKeyColumn;

场景2：结合已有的逗号分隔星期列 #

如果你的表中还有一个存储逗号分隔星期名称的字段（比如WeekList，值类似"SUNDAY, MONDAY"），我们需要先拆分这个字段的值，再和布尔字段的结果合并去重，最后拼接：

SQL Server 示例代码 #

SELECT
    CASE WHEN EveryDay = 1 THEN 'Every Day'
         ELSE
             STRING_AGG(DISTINCT DayName, ', ') WITHIN GROUP (ORDER BY DayOrder)
         END AS Days
FROM (
    -- 从布尔字段提取选中的星期名称
    SELECT
        YourPrimaryKeyColumn,
        CASE
            WHEN SUN = 1 THEN 'SUNDAY'
            WHEN MON = 1 THEN 'MONDAY'
            WHEN TUE = 1 THEN 'TUESDAY'
            WHEN WED = 1 THEN 'WEDNESDAY'
            WHEN THU = 1 THEN 'THURSDAY'
            WHEN FRI = 1 THEN 'FRIDAY'
            WHEN SAT = 1 THEN 'SATURDAY'
        END AS DayName,
        CASE
            WHEN SUN = 1 THEN 1
            WHEN MON = 1 THEN 2
            WHEN TUE = 1 THEN 3
            WHEN WED = 1 THEN 4
            WHEN THU = 1 THEN 5
            WHEN FRI = 1 THEN 6
            WHEN SAT = 1 THEN 7
        END AS DayOrder
    FROM YourTableName
    WHERE EveryDay <> 1

    UNION ALL

    -- 拆分逗号分隔的WeekList字段
    SELECT
        YourPrimaryKeyColumn,
        LTRIM(RTRIM(value)) AS DayName,
        CASE LTRIM(RTRIM(value))
            WHEN 'SUNDAY' THEN 1
            WHEN 'MONDAY' THEN 2
            WHEN 'TUESDAY' THEN 3
            WHEN 'WEDNESDAY' THEN 4
            WHEN 'THURSDAY' THEN 5
            WHEN 'FRIDAY' THEN 6
            WHEN 'SATURDAY' THEN 7
        END AS DayOrder
    FROM YourTableName
    CROSS APPLY STRING_SPLIT(WeekList, ',')
    WHERE EveryDay <> 1 AND WeekList IS NOT NULL AND WeekList <> ''
) AS CombinedDays
GROUP BY YourPrimaryKeyColumn;

关键要点总结 #

• CASE表达式是返回单个值的工具，不能像编程语言那样用来赋值变量，我们用它生成每个星期的名称后，再通过聚合函数拼接
• 优先处理EveryDay=1的情况，避免多余的星期拼接
• 用STRING_AGG/GROUP_CONCAT时可以指定排序规则，让结果更符合阅读习惯
• 对于逗号分隔的字段，需要先拆分再合并，记得用DISTINCT避免重复的星期名称

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