如何找到2个以上用户的匹配时间间隔 [英] How to find matching time intervals for more than 2 users
问题描述
从不同用户的给定时间间隔中找到最佳适合时间.
Find best suitable time from given time interval of different users.
Rows: 5
fid userid FromDateTime ToDateTime flag
62 1 2012-07-18 01:48:20 2012-07-18 02:55:20 1
63 1 2012-07-18 10:30:46 2012-07-18 12:54:46 1
64 1 2012-07-18 18:50:24 2012-07-18 20:35:24 1
67 1 2012-07-18 15:03:36 2012-07-18 16:03:36 1
68 2 2012-07-18 21:10:47 2012-07-18 23:10:47 1
上表显示了不同用户可用的不同空闲时间,例如:
Above table show different free timesperiods available for different user, for examaple:
user1
在
2012-07-18 01:48:20 to 2012-07-18 02:55:20 ,
2012-07-18 10:30:46 to 2012-07-18 12:54:46
......
user 2
仅在此时间段内免费:
user 2
is only free between this time period:
2012-07-18 21:10:47 to 2012-07-18 23:10:47
现在,我想找出两个用户都可以安排会议的最佳时间间隔.
Now I want to find out one best time interval in which both user can schedule their meeting.
推荐答案
要查找user1和user2何时都空闲,请尝试以下操作:
To find when both user1 and user2 are free, please try below:
select
a.datetime_start as user1start,a.datetime_end as user1end,
b.datetime_start as user2start,b.datetime_end as user2end ,
case when a.datetime_start > b.datetime_start then a.datetime_start
else b.datetime_start end as avail_start,
case when a.datetime_end>b.datetime_end then b.datetime_end
else a.datetime_end end as avail_end
from users a inner join users b on
a.datetime_start<=b.datetime_end and a.datetime_end>=b.datetime_start
and a.userid={user1} and b.userid={user2}
要比较两个以上的用户,请尝试以下操作:
EDITED: For comparing more than 2 users,pls try below:
select max(datetime_start) as avail_start,min(datetime_end) as avail_end
from(
select *,
@rn := CASE WHEN @prev_start <=datetime_end and @prev_end >=datetime_start THEN @rn ELSE @rn+1 END AS rn,
@prev_start := datetime_start,
@prev_end := datetime_end
from(
select * from users2 m
where exists ( select null
from users2 o
where o.datetime_start <= m.datetime_end and o.datetime_end >= m.datetime_start
and o.id <> m.id
)
and m.userid in (2,4,3,5)
order by m.datetime_start) t,
(SELECT @prev_start := -1, @rn := 1, @prev_end=-1) AS vars
) c
group by rn
having count(rn)=4 ;
需要根据用户数量更改m.userid in (2,4,3,5)
和having count(rn)=4
.
Need to change m.userid in (2,4,3,5)
and having count(rn)=4
according to number of users.
这篇关于如何找到2个以上用户的匹配时间间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!