SQL实现指定服务商员工按小时维度周/月度预约报表

核心需求 #

• 为指定服务提供商生成全体员工的周/月度统计报表
• 统计粒度精确到1小时，逐时段标注对应员工的状态：已预约 / 空闲
• 需兼容单条预约覆盖多个连续1小时时段的场景（例如示例中ID=2的长时段预约）
• 最终输出为横向排布的时段状态表格

现有数据源 #

当前使用的源数据查询SQL如下：

SELECT 
       DATE(start_time) AS "date"
      ,DAYNAME(start_time) AS "weekday"
      ,account.name AS "employee"
      ,appointment.id AS "appointment"
      ,appointment.start_time
      ,appointment.end_date
FROM appointment
   INNER JOIN employee ON appointment.employee = employee.id
   INNER JOIN account ON employee.account = account.id
ORDER BY date

该查询返回纵向排列的预约明细数据，字段包含：预约日期、星期值、员工姓名、预约ID、预约开始时间、预约结束时间，无法直接生成目标横向小时粒度报表。

实现逻辑 #

要得到目标格式的表格，按以下步骤处理即可：

• 生成基础底表：先确定报表的统计时间范围，按1小时间隔切分出周期内所有时段，再关联所有需要统计的员工，生成「日期+时段+员工」的全量组合行，所有行初始状态默认标记为空闲。如果直接在数据库层实现，用递归CTE就能快速生成连续小时序列，不需要额外导出数据到外部工具处理。
• 拆分跨时段预约：针对原始查询返回的预约明细，把每条预约按1小时粒度做切片——比如13:00开始、16:00结束的3小时预约，就拆成13:00-14:00、14:00-15:00、15:00-16:00三条对应时段的记录，解决长预约只匹配到开始时段的问题。
• 匹配更新状态：把拆分后的预约时段记录和基础底表做关联，匹配到相同员工、相同日期、相同时段的行，将对应状态更新为已预约。
• 行列转置输出：把原本按行存储的小时时段字段转为表格列，每一列对应一个小时时段，行维度保留日期、星期、员工信息，单元格填充对应时段的状态值，即可得到目标格式的报表。

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