SQL Server将带起止日期的酒店预订记录转换为日历格式方案咨询

高效实现方案 #

核心思路是用预生成的日期维度表替代逐日期循环查询，配合交叉连接+条件匹配+行转列完成转换，性能比逐行逐单元查询至少提升1~2个数量级。

步骤1：生成查询周期内的日期序列 #

如果已经有现成的日期维度表可以直接用，没有的话可以用递归CTE快速生成指定周期的所有日期：

DECLARE @StartDate DATE = '2021-07-01', @EndDate DATE = '2021-08-15';
WITH DateSeries AS (
    SELECT @StartDate AS CalDate
    UNION ALL
    SELECT DATEADD(DAY, 1, CalDate) FROM DateSeries WHERE CalDate < @EndDate
)
SELECT * INTO #TempCal FROM DateSeries OPTION (MAXRECURSION 0);

注：MAXRECURSION 0 是取消递归CTE的100层限制，可支持超过3个月的日期跨度

步骤2：匹配客房单元、日期和对应状态 #

先把所有客房单元和日期做笛卡尔积，再和预订记录左关联，一次性得到每个单元每天的状态，避免逐次查询：

WITH UnitList AS (
    SELECT DISTINCT UnitId FROM BookingTable -- 替换为你的预订记录表名
)
SELECT 
    u.UnitId,
    c.CalDate,
    CASE 
        WHEN b.State = 1 THEN 'R'
        WHEN b.State = 2 THEN 'O'
        ELSE '' END AS Status
FROM UnitList u
CROSS JOIN #TempCal c
LEFT JOIN BookingTable b 
    ON u.UnitId = b.UnitId 
    AND c.CalDate >= CAST(b.[From] AS DATE) 
    AND c.CalDate <= CAST(b.[To] AS DATE)

注意SQL Server中From和To是保留关键字，查询时要用方括号括起来

步骤3：行转列生成日历格式输出 #

用PIVOT语法实现日期转列，如需动态适配不同查询周期的所有日期列，可拼接动态SQL执行：

DECLARE @Col NVARCHAR(MAX), @Sql NVARCHAR(MAX);
SELECT @Col = STRING_AGG(QUOTENAME(FORMAT(CalDate, 'yyyyMMdd')), ',') FROM #TempCal;

SET @Sql = N'
WITH UnitList AS (
    SELECT DISTINCT UnitId FROM BookingTable
)
SELECT * FROM (
    SELECT 
        u.UnitId AS Unit,
        FORMAT(c.CalDate, ''yyyyMMdd'') AS CalStr,
        CASE 
            WHEN b.State = 1 THEN ''R''
            WHEN b.State = 2 THEN ''O''
            ELSE '''' END AS Status
    FROM UnitList u
    CROSS JOIN #TempCal c
    LEFT JOIN BookingTable b 
        ON u.UnitId = b.UnitId 
        AND c.CalDate >= CAST(b.[From] AS DATE) 
        AND c.CalDate <= CAST(b.[To] AS DATE)
) AS Source
PIVOT (
    MAX(Status) FOR CalStr IN (' + @Col + N')
) AS PivotTable
ORDER BY Unit
';
EXEC sp_executesql @Sql;

额外性能优化建议 #

• 高频做这类查询的场景，建议创建持久化的日期维度表并提前建好索引，无需每次查询时动态生成
• 给预订记录表的UnitId、[From]、[To]字段建联合索引，可大幅提升关联匹配效率
• 数据量较大时，先按查询的日期范围过滤预订记录，再做关联匹配，减少参与计算的数据量

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