SQL分组同时间段日程并排序：查询优化与排序问题求助

使用SQL Server 15.0.2000.5，现有StudentSchedule表结构及数据如下：

期望转换为以下格式（注：原示例中31号的StartTime/EndTime为笔误，已修正为对应表中数据）：

已通过以下查询实现需求：

Select s.StudentScheduleId, 
    STRING_AGG(s.[Day], '/') AS Schedule, 
    s.StartTime, 
    s.EndTime
From
(
(Select s.StudentScheduleId, 'M' As [Day], s.MondayStartTime As StartTime, s.MondayEndTime As EndTime
From StudentSchedule s
Where s.Monday = 1)
UNION
(Select s.StudentScheduleId, 'T' As [Day], s.TuesDayStartTime As StartTime, s.TuesdayEndTime As EndTime
From StudentSchedule s
Where s.Tuesday = 1)
UNION
(Select s.StudentScheduleId, 'W' As [Day], s.WednesdayStartTime As StartTime, s.WednesdayEndTime As EndTime
From StudentSchedule s
Where s.Wednesday = 1)
UNION
(Select s.StudentScheduleId, 'Th' As [Day], s.ThursdayStartTime As StartTime, s.ThursdayEndTime As EndTime
From StudentSchedule s
Where s.Thursday = 1)
UNION
(Select s.StudentScheduleId, 'F' As [Day], s.FridayStartTime As StartTime, s.FridayEndTime As EndTime
From StudentSchedule s
Where s.Friday = 1)
) As s
Group By s.StudentScheduleId, s.StartTime, s.EndTime

目前存在两个问题：

1. 若表格增长至几十万条数据，是否有性能更优、写法更简洁的实现方式？
2. STRING_AGG生成的Schedule列顺序随机，需按M/T/W/Th/F的逻辑顺序排序，如何实现？

问题1：优化性能与简化写法 #

原查询通过多次UNION分支扫描表，数据量增大后性能会明显下降。改用CROSS APPLY + 值构造函数，仅需扫描表一次，写法更简洁易维护：

SELECT 
    ss.StudentScheduleId,
    STRING_AGG(day_info.DayAbbr, '/') AS Schedule,
    day_info.StartTime,
    day_info.EndTime
FROM StudentSchedule ss
CROSS APPLY (
    VALUES
        ('M', Monday, MondayStartTime, MondayEndTime),
        ('T', Tuesday, TuesdayStartTime, TuesdayEndTime),
        ('W', Wednesday, WednesdayStartTime, WednesdayEndTime),
        ('Th', Thursday, ThursdayStartTime, ThursdayEndTime),
        ('F', Friday, FridayStartTime, FridayEndTime)
) AS day_info(DayAbbr, DayFlag, StartTime, EndTime)
WHERE day_info.DayFlag = 1 -- 仅保留有课程的天数
GROUP BY ss.StudentScheduleId, day_info.StartTime, day_info.EndTime

性能优势： #

• 单次扫描原表，避免UNION多分支的重复扫描开销
• CROSS APPLY的横向扩展逻辑在执行计划中更高效
• 代码结构紧凑，减少重复冗余的SELECT语句

问题2：控制STRING_AGG的排序顺序 #

STRING_AGG支持通过WITHIN GROUP (ORDER BY ...)子句指定聚合顺序，只需给每个星期缩写定义排序权重即可：

SELECT 
    ss.StudentScheduleId,
    STRING_AGG(day_info.DayAbbr, '/') WITHIN GROUP (ORDER BY day_info.SortOrder) AS Schedule,
    day_info.StartTime,
    day_info.EndTime
FROM StudentSchedule ss
CROSS APPLY (
    VALUES
        ('M', Monday, MondayStartTime, MondayEndTime, 1),
        ('T', Tuesday, TuesdayStartTime, TuesdayEndTime, 2),
        ('W', Wednesday, WednesdayStartTime, WednesdayEndTime, 3),
        ('Th', Thursday, ThursdayStartTime, ThursdayEndTime, 4),
        ('F', Friday, FridayStartTime, FridayEndTime, 5)
) AS day_info(DayAbbr, DayFlag, StartTime, EndTime, SortOrder)
WHERE day_info.DayFlag = 1
GROUP BY ss.StudentScheduleId, day_info.StartTime, day_info.EndTime

实现逻辑： #

• 在值构造函数中新增SortOrder字段，按M(1)→T(2)→W(3)→Th(4)→F(5)的顺序赋值
• 通过WITHIN GROUP (ORDER BY SortOrder)强制聚合后的星期缩写按指定逻辑顺序排列

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