MySQL技术问询：如何按房间统计总预订数与续订预订数

嘿，我来帮你搞定这个MySQL统计需求！下面是整理好的方案：

原始数据表 #

假设我们的表名为bookings，数据如下：

需求说明 #

我们需要按房间统计两个核心指标：

• total_bookings：该房间的总预订次数
• total_renewal：该房间的续订预订数（同一用户重复预订同一房间的次数，比如用户在房间订了3次，就贡献2次续订）

解决方案SQL语句 #

SELECT 
    room_id,
    SUM(user_bookings) AS total_bookings,
    SUM(CASE WHEN user_bookings > 1 THEN user_bookings - 1 ELSE 0 END) AS total_renewal
FROM (
    -- 先统计每个用户在每个房间的预订次数
    SELECT 
        room_id,
        user_id,
        COUNT(*) AS user_bookings
    FROM bookings
    GROUP BY room_id, user_id
) AS user_room_summary
GROUP BY room_id;

语句解释 #

1. 子查询user_room_summary：先按room_id和user_id分组，统计每个用户在对应房间的预订次数user_bookings，这一步是为了单独计算每个用户的重复预订情况。
2. 外层查询：
   • SUM(user_bookings)：把每个用户的预订次数相加，直接得到房间的总预订数。
   • SUM(CASE...)：对每个用户的预订次数做判断，如果超过1次，就取次数-1（也就是该用户的续订次数），否则取0，最后求和得到整个房间的总续订数。

执行结果 #

执行上述语句后，会得到符合预期的结果：

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