酒店预订系统SQL查询需求：按日期和房型统计已售房间数

嘿，我完全懂你现在卡在哪了——能生成日期序列、算出每日总销量，但就是没法让每个日期下的所有房型都凑齐，没卖出去的房型没法显示0对吧？这其实是SQL里处理维度补全的经典场景，核心思路就是先把所有可能的「日期+房型」组合都造出来，再和实际销售数据关联统计。

下面给你一步步拆解解决方法：

核心思路 #

要实现“每个日期每种房型都有一行，无销量显示0”，必须先构建一个包含所有日期和所有房型的完整维度表（用交叉连接生成），再左连接你的销售数据，最后把NULL值转成0。

具体SQL示例（以PostgreSQL为例，其他数据库可调整） #

假设你已经有：

• 一个生成日期范围的方法（你已经实现了，这里用CTE示例）
• 存储房型信息的表 room_types（包含room_type_id、room_type_name等字段）
• 存储预订数据的表 bookings（包含booking_id、room_type_id、check_in_date或booking_date、quantity等字段）

-- 1. 生成需要统计的日期范围（替换成你自己的日期区间）
WITH date_range AS (
    SELECT generate_series(
        '2024-05-01'::DATE,
        '2024-05-10'::DATE,
        '1 day'::INTERVAL
    ) AS stat_date
),
-- 2. 获取所有房型的完整列表
all_room_types AS (
    SELECT room_type_id, room_type_name 
    FROM room_types
)
-- 3. 交叉连接生成所有日期+房型的组合，再左连接销售数据统计
SELECT
    dr.stat_date::DATE,
    art.room_type_id,
    art.room_type_name,
    -- 用COALESCE把NULL转成0，COUNT或SUM根据你的业务选
    COALESCE(SUM(b.quantity), 0) AS sold_rooms
FROM date_range dr
-- 关键：交叉连接得到所有可能的日期+房型组合
CROSS JOIN all_room_types art
-- 左连接预订数据，关联条件要同时匹配日期和房型
LEFT JOIN bookings b
    ON dr.stat_date::DATE = b.check_in_date  -- 这里按入住日期统计，可换成预订日期
    AND art.room_type_id = b.room_type_id
-- 按日期和房型分组统计
GROUP BY dr.stat_date, art.room_type_id, art.room_type_name
-- 排序让结果更清晰
ORDER BY dr.stat_date, art.room_type_id;

关键细节说明 #

• CROSS JOIN的作用：这一步是核心，它会把日期序列的每一行和房型表的每一行做组合，确保不管有没有销量，每个日期的每种房型都有一条记录。
• COALESCE函数：左连接后，没有对应销售数据的行统计结果会是NULL，用这个函数把NULL转换成0，完美实现“无销量显示0”的需求。
• 统计逻辑调整：如果你的预订表中一个订单只对应一间房，用COUNT(b.booking_id)也可以；如果存在一个订单订多间的情况，一定要用SUM(b.quantity)来统计实际售出数量。
• 日期生成适配：不同数据库生成日期序列的方式不同：
  • MySQL：用递归CTE + DATE_ADD
  • SQL Server：用递归CTE + DATEADD
  • Oracle：用CONNECT BY或递归CTE

特殊情况处理 #

如果没有单独的room_types表，也可以从预订表中提取所有出现过的房型：

all_room_types AS (
    SELECT DISTINCT room_type_id, room_type_name 
    FROM bookings
)

但这种方式有个缺点：如果有新房型还没被预订过，就不会出现在结果里，所以建议还是维护一个独立的房型表更稳妥。

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