酒店系统的相关问题:统计每天在同一时间段内服务房间最多的服务生。
分析:当一个房间的服务开始之后,只要第2个房间的服务开始时间小于(早于)或者等于第1个房间的开始时间,并且结束时间大于(晚于)第1个房间的开始时间的,就说明存在重叠部分。
首先查出同一时间范围内的:
SELECT * FROM waiters a,waiters b
where a.waiter_name = b.waiter_name
and b.start_time <= a.start_time
and b.end_time > a.start_time
order by a.waiter_name,a.start_time,a.end_time,a.room_id
在未分组前是如下情况:
这样满足上面的条件的,孙静有8组,张岚有2组。
接下来进行分组,即:
SELECT a.room_id,a.waiter_name,a.start_time,a.end_time FROM waiters a,waiters b
where a.waiter_name = b.waiter_name
and b.start_time <= a.start_time
and b.end_time > a.start_time
group by a.room_id,a.waiter_name,a.start_time,a.end_time
order by a.waiter_name,a.start_time,a.end_time,a.room_id
;
即对上图的一个简化。
然后再统计每一组内房间重叠的数量,即在上面的图的基础上做count(*)运算。
--count(*)即为求每一组内的数量;max(a.room_id)是查每组内房间号最大的(这个不是重点)
SELECT a.room_id,a.waiter_name,a.start_time,a.end_time,count(*) as tally,max(a.room_id) room_id FROM waiters a,waiters b
where a.waiter_name = b.waiter_name
and b.start_time <= a.start_time
and b.end_time > a.start_time
group by a.room_id,a.waiter_name,a.start_time,a.end_time
order by a.waiter_name,a.start_time,a.end_time,a.room_id
;
从上表中,只要在按照姓名分一下组,然后取出每组tally的最大值,就可以计算出每人同时服务房间的最大数了。
select tt.waiter_name,max(tt.tally) as tally
from(
SELECT a.room_id,a.waiter_name,a.start_time,a.end_time,count(*) as tally FROM waiters a,waiters b
where a.waiter_name = b.waiter_name
and b.start_time <= a.start_time
and b.end_time > a.start_time
group by a.room_id,a.waiter_name,a.start_time,a.end_time
order by a.waiter_name,a.start_time,a.end_time,room_id
) as tt
group by tt.waiter_name
;