+----+---------+---------+
| id | room_id | user_id |
+----+---------+---------+
| 1 | 5 | 10 |
| 2 | 5 | 20 |
| 3 | 5 | 20 |
| 4 | 6 | 30 |
| 5 | 6 | 40 |
| 6 | 6 | 50 |
| 7 | 7 | 10 |
| 8 | 7 | 10 |
| 9 | 8 | 20 |
| 10 | 9 | 10 |
| 11 | 9 | 10 |
| 12 | 9 | 10 |
+----+---------+---------+
我想选择每个房间都有预订数和续订预订数的顶级客房
为了得到这个结果
+---------+----------------+---------------+
| room_id | total_bookings | total_renewal |
+---------+----------------+---------------+
| 5 | 3 | 1 |
| 6 | 3 | 0 |
| 7 | 2 | 1 |
| 8 | 1 | 0 |
| 9 | 3 | 2 |
| 5 | 1 | 0 |
+---------+----------------+---------------+
而这个实时mysql在这里https://paiza.io/projects/Ao2C6d6pgE133QXQAl03Ug?language=mysql
........
考虑到您对续订的要求尚不明确，使用纯sql可以完成以下工作：
select
s.room_id,
sum(s.total_bookings) as total_bookings,
sum(case when s.total_bookings > 1 then s.total_bookings - 1 else 0 end) as total_renewal
from (
select room_id, user_id, count(*) as total_bookings
from booking
group by room_id, user_id
) s
group by s.room_id;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句