如何编写按起止时间分组、汇总拼接对应星期字符串的SQL查询
按时间维度聚合课程排期的SQL实现方案
核心实现思路
要实现分组聚合和星期值拼接的联动逻辑,本质是以「课程ID+排期开始时间+排期结束时间」作为统一分组维度,对每个分组下的关联星期值做字符串拼接。你已经掌握的STUFF+FOR XML PATH拼接逻辑可以直接复用,只需要调整子查询的关联匹配条件,就能满足需求。
完整落地代码
SELECT base.ClassId, base.StartTime, base.EndTime, -- 独立列展示聚合后的星期集合 STUFF(( SELECT '/' + CASE WHEN d.Name = 'Thursday' THEN SUBSTRING(d.Name, 1, 2) WHEN d.Name = 'Sunday' THEN 'U' WHEN d.Name != '' THEN SUBSTRING(d.Name, 1, 1) ELSE NULL END FROM MeetingTime m INNER JOIN DaysOfWeek d ON d.Id = m.DayOfWeekId -- 关键匹配条件:和外层分组的时间维度完全对齐,确保拼接的是同时间段的星期值 WHERE m.ClassId = base.ClassId AND m.StartTime = base.StartTime AND m.EndTime = base.EndTime FOR XML PATH('') ), 1, 1, '') AS WeekDayCollection, -- 可选:拼接成和示例一致的完整格式化字符串 CONVERT(varchar(5), base.StartTime, 108) + '-' + CONVERT(varchar(5), base.EndTime, 108) + ' ' + STUFF(( SELECT '/' + CASE WHEN d.Name = 'Thursday' THEN SUBSTRING(d.Name, 1, 2) WHEN d.Name = 'Sunday' THEN 'U' WHEN d.Name != '' THEN SUBSTRING(d.Name, 1, 1) ELSE NULL END FROM MeetingTime m INNER JOIN DaysOfWeek d ON d.Id = m.DayOfWeekId WHERE m.ClassId = base.ClassId AND m.StartTime = base.StartTime AND m.EndTime = base.EndTime FOR XML PATH('') ), 1, 1, '') AS FormattedSchedule FROM ( -- 先提取所有不重复的排期时间组合,作为分组的基础维度 SELECT DISTINCT t.ClassId, t.StartTime, t.EndTime FROM MeetingTime t INNER JOIN DaysOfWeek d ON d.Id = t.DayOfWeekId WHERE t.ClassId = 8 -- 替换为实际查询的课程ID ) base
效果匹配说明
- 当同一时间区间对应多个星期排期时,会自动把对应星期缩写拼接为单条结果,比如周一到周五都是09:00-15:35的排期,会输出
09:00-15:35 M/T/W/Th/F的格式化结果 - 当存在多套不同时间区间的排期时,会自动按时间拆分多条结果,比如07:35-14:15对应周一/周二/周三、08:00-14:15对应周四/周五的场景,会输出两条独立的聚合记录
- 当所有排期的时间区间都完全不重复时,每个时间区间会单独输出对应的单个星期值,完全匹配需求边界
注意事项
- 代码中统一了关联表名(你之前示例中同时出现了
DaysOfWeek和DayOfWeek的不同表名,实际使用时需要替换为数据库中的真实表名) - 相比在C#代码层做循环聚合,SQL侧直接输出聚合结果可以减少应用层的数据传输量和循环计算开销,在排期数据量较大时性能更优
内容的提问来源于stack exchange,提问作者cmartin
相关产品推荐
相关产品推荐

