SQL多表关联：统计每日各酒店预订量并生成指定结果

核心思路 #

要实现需求，需要先构建12月所有日期与所有酒店的完整组合，再左关联预订数据统计数量，确保无预订的条目也能显示（计数为0，HotelResourceId为NULL）。

最终SQL语句 #

SELECT
    cd.Date,
    hr.HotelId,
    hr.Name,
    -- 无预订时返回NULL，否则取预订表的HotelResourceId
    CASE WHEN hrb.HotelResourceId IS NULL THEN NULL ELSE hrb.HotelResourceId END AS HotelResourceId,
    -- 统计预订数，无预订时自动返回0
    COUNT(hrb.BookingId) AS Count
FROM
    CalendarData cd
-- 生成日期与酒店的全量组合
CROSS JOIN
    HotelResource hr
-- 左关联预订表，保留所有日期-酒店组合
LEFT JOIN
    HotelResourcesBookings hrb
    ON cd.Date = hrb.BookingDate  -- 请根据实际预订表日期字段名调整
    AND hr.HotelResourceId = hrb.HotelResourceId  -- 请根据实际关联字段调整
WHERE
    MONTH(cd.Date) = 12  -- 筛选12月数据，需指定年份可加 AND YEAR(cd.Date) = 2024
GROUP BY
    cd.Date,
    hr.HotelId,
    hr.Name,
    CASE WHEN hrb.HotelResourceId IS NULL THEN NULL ELSE hrb.HotelResourceId END
ORDER BY
    cd.Date,
    hr.HotelId;

关键细节说明 #

• CROSS JOIN：确保12月的每一天和每一家酒店都能生成一条基础记录，避免遗漏无预订的日期/酒店
• LEFT JOIN：关联预订数据时，即使没有匹配的预订记录，基础的日期-酒店条目也会被保留
• COUNT(hrb.BookingId)：利用COUNT忽略NULL值的特性，无预订时自动返回0；如果用COUNT(*)会把无预订的条目统计为1，这里必须用具体的预订主键字段
• CASE语句：严格实现“无预订时HotelResourceId为NULL”的要求
• WHERE筛选：如果CalendarData包含多个年份的12月数据，一定要加上年份过滤条件，避免数据混乱

适配调整提示 #

请根据你的实际表结构调整以下部分：

• 预订表中的日期字段（比如可能是Date而非BookingDate）
• 酒店表与预订表的关联字段（比如如果酒店表的主键是HotelId，则关联条件改为hr.HotelId = hrb.HotelId）

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