如何在T-SQL中操作字符串实现商家营业时间按天合并去重

现有营业时间表数据 #

建表及测试数据SQL #

create table open_times (business_id int, open_day varchar(10), open_time varchar(10), close_day varchar(10), close_time varchar(10) )
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (1, 'FRIDAY', '08:00', 'FRIDAY', '12:00')
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (1, 'FRIDAY', '13:00', 'FRIDAY', '17:00')
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (1, 'MONDAY', '08:00', 'MONDAY', '17:00')
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (2, 'SATURDAY', '08:00', 'SATURDAY', '16:00')
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (2, 'SUNDAY', '08:00', 'SUNDAY', '16:00')
insert into open_times (business_id, open_day, open_time, close_day, close_time) values (3, 'MONDAY', '08:00', 'MONDAY', '16:00')

-- 清理测试数据用
-- drop table open_times

需求说明 #

为每个商家生成格式化的营业时长字符串，要求按天分组，同一天多个时段仅展示一次星期缩写：

• 当前错误输出（商家1）：Fr 08:00-12:00; Fr 13:00-17:00; Mo 08:00-17:00
• 期望输出（商家1）：Fr 08:00-12:00, 13:00-17:00; Mo 08:00-17:00

实现方案 #

核心思路是分两层拼接：先按「商家+星期」分组拼接同天的多个时段，再按「商家」分组拼接不同天的营业信息。
完整SQL代码如下：

SELECT 
    business_id,
    STUFF((
        SELECT '; ' + day_opening_str
        FROM (
            -- 内层分组：按商家+星期拼接同天所有时段
            SELECT 
                business_id,
                open_day,
                -- 生成单天的营业字符串：星期缩写 + 多时段逗号拼接
                (CASE open_day
                    WHEN 'FRIDAY' THEN 'Fr'
                    WHEN 'MONDAY' THEN 'Mo'
                    WHEN 'TUESDAY' THEN 'Tu'
                    WHEN 'WEDNESDAY' THEN 'We'
                    WHEN 'THURSDAY' THEN 'Th'
                    WHEN 'SATURDAY' THEN 'Sa'
                    ELSE 'Su' 
                END) + ' ' + STUFF((
                    SELECT ', ' + open_time + '-' + close_time
                    FROM open_times ot_inner
                    WHERE ot_inner.business_id = ot1.business_id 
                      AND ot_inner.open_day = ot1.open_day
                    FOR XML PATH(''), TYPE
                ).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS day_opening_str
            FROM open_times ot1
            GROUP BY business_id, open_day
        ) day_group_result
        WHERE day_group_result.business_id = main.business_id
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS opening_hours
FROM open_times main
GROUP BY business_id

最终输出结果 #

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