SQL如何基于周日日历表生成30分钟间隔可预约时段表

现有基础信息 #

已存在的周日日期表 #

• 表名：dbo.Calendar
• 字段：[DatesInCalendar]，类型为DateTime，存储格式yyyy-MM-dd，仅保存所有周日日期，示例数据：

  2022-06-19
  2022-06-26
  ...
  

待写入的可预约时段表 #

• 表名：dbo.BookableTimeSlots
• 字段定义：
  • [TimeSlots]：类型DateTime，存储格式yyyy-MM-dd hh:mi:ss
  • [Booked]：类型Bit
  • [BookedBy]：类型NvarChar(10)

数据生成规则 #

• 为dbo.Calendar中的每一个周日，生成当日10:00:00至16:00:00范围内、间隔30分钟的所有时段，单天生成的时段示例：

  2022-06-19 10:00:00
  2022-06-19 10:30:00
  ...
  2022-06-19 16:00:00
  

• 插入数据时[Booked]固定为0，[BookedBy]固定为NULL。

已有参考代码 #

之前通过循环方式完成了dbo.Calendar的日期填充，代码如下，但无法直接改造实现目标表写入：

USE [MyDatabase]
GO

declare @startDate date,@enddate date
set @startDate='2022-06-01'
set @enddate='2025-06-01'

while @startDate<=@enddate
begin
if(DATENAME(dw,@startDate)='Sunday')
INSERT INTO [dbo].[Calendar]
           ([DatesInCalendar])
     VALUES
           (convert(date,@startDate,103))
set @startDate=DATEADD(DD,1,@startDate) 
end
GO

实现代码 #

不需要写多层循环，直接通过序列关联的方式批量生成数据，性能远高于逐行循环写入，以下是SQL Server环境下可直接运行的代码：

方案1：递归CTE实现（无需依赖系统表） #

USE [MyDatabase]
GO

;WITH TimeOffset AS (
    -- 生成0到360分钟的30分钟间隔偏移，对应10点到16点共13个时段
    SELECT 0 AS MinuteOffset
    UNION ALL
    SELECT MinuteOffset + 30 FROM TimeOffset WHERE MinuteOffset < 360
)
INSERT INTO dbo.BookableTimeSlots
    (TimeSlots, Booked, BookedBy)
SELECT
    DATEADD(MINUTE, o.MinuteOffset, CAST(c.DatesInCalendar AS DATETIME) + CAST('10:00:00' AS DATETIME)),
    0,
    NULL
FROM dbo.Calendar c
CROSS JOIN TimeOffset o
OPTION (MAXRECURSION 0)
GO

方案2：系统表生成序列（无递归，性能更稳定） #

USE [MyDatabase]
GO

;WITH NumSeq AS (
    -- 生成0-12的连续数字，对应13个30分钟间隔
    SELECT TOP 13 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) -1 AS Seq
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
)
INSERT INTO dbo.BookableTimeSlots
    (TimeSlots, Booked, BookedBy)
SELECT
    DATEADD(MINUTE, n.Seq * 30, CAST(c.DatesInCalendar AS DATETIME) + CAST('10:00:00' AS DATETIME)),
    0,
    NULL
FROM dbo.Calendar c
CROSS JOIN NumSeq n
GO

执行提示：运行前请确认目标表中不存在重复的时段记录，避免唯一约束冲突。如果表中已有部分数据，可以在INSERT后加WHERE NOT EXISTS判断跳过已存在的时段。

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