SQL从日期范围中选择
问题描述:
我有一个包含开始时间,停止时间和MAC地址的RADIUS会话记录表。我有一个要求,收集两个时间范围内联机的用户列表。我相信,在与下面的查询的时间范围内,我让所有用户的列表在线:SQL从日期范围中选择
SELECT s_session_id, s_start_time, s_stop_time, s_calling_station_id
FROM sessions
WHERE (
("2015-10-01 08:00:00" BETWEEN s_start_time AND s_stop_time OR "2015-10-01 08:30:00" BETWEEN s_start_time AND s_stop_time)
OR
("2015-10-01 12:00:00" BETWEEN s_start_time AND s_stop_time OR "2015-10-01 12:30:00" BETWEEN s_start_time AND s_stop_time)
)
ORDER BY s_start_time;
但下一步,在都时期隔离细节只有那些用户在线时,躲避我。我得到最接近的是
GROUP BY s_calling_station_id HAVING COUNT(s_calling_station_id) > 1
但这并没有提供我所有的会议的细节。
小提琴是在这里:http://sqlfiddle.com/#!9/1df471/1
感谢任何帮助!
答
尽管这个问题已经有一个公认的答案,我想添加这个(它避免了重复和拉从符合条件的所有会话的会话表中的数据):
首先,创建包含过滤后的数据(即具有两个间隔连接的MAC地址的表:
create table temp_sessions
select s1.s_calling_station_id
, if(@t1_1 between s1.s_start_time and s1.s_stop_time or @t1_2 between s1.s_start_time and s1.s_stop_time, s1.s_session_id, null) as s_1
, if(@t2_1 between s2.s_start_time and s2.s_stop_time or @t2_2 between s2.s_start_time and s2.s_stop_time, s2.s_session_id, null) as s_2
from -- I use user variables because it will make easier to modify the time intervals if needed
(select @t1_1 := '2015-10-01 08:00:00', @t1_2 := '2015-10-01 08:30:00'
, @t2_1 := '2015-10-01 12:00:00', @t2_2 := '2015-10-01 12:30:00') as init
, sessions as s1
inner join sessions as s2
on s1.s_calling_station_id = s2.s_calling_station_id
and s1.s_session_id != s2.s_session_id
having s_1 is not null and s_2 is not null;
而现在,只需使用此表来获得你所需要的:
select sessions.*
from sessions
inner join (
select s_calling_station_id, s_1 as s_session_id
from temp_sessions
union
select s_calling_station_id, s_2 as s_session_id
from temp_sessions
) as a using (s_calling_station_id, s_session_id);
+0
谢谢,在我看来这是一个更好的解决方案,因为它将每个会话放在自己的记录上,不需要后处理。此外,它捕捉到某人只上线一次的情况,但会话重叠了两个时间范围。 – miken32
答
使用自联接。使用列别名,以便您可以使用不同的名称访问每个会话中的列。
SELECT s1.s_calling_station_id,
s1.s_session_id AS s1_session_id, s1.s_start_time AS s1_start_time, s1.s_stop_time AS s1_stop_time,
s2.s_session_id AS s2_session_id, s2.s_start_time AS s2_start_time, s2.s_stop_time AS s2_stop_time
FROM sessions AS s1
JOIN sessions AS s2
ON s1.s_calling_station_id = s2.s_calling_station_id
AND s1.s_session_id != s2.s_session_id
WHERE ("2015-10-01 08:00:00" BETWEEN s1.s_start_time AND s1.s_stop_time OR "2015-10-01 08:30:00" BETWEEN s1.s_start_time AND s1.s_stop_time)
AND
("2015-10-01 12:00:00" BETWEEN s2.s_start_time AND s2.s_stop_time OR "2015-10-01 12:30:00" BETWEEN s2.s_start_time AND s2.s_stop_time)
可我只是说,谁downvote提供一个明确的要求问题,示例代码,链接,小提琴等不加评论的人是世界上最糟糕的人。严重的是,WTF在这个问题上是错误的? – miken32
@Barranka不是针对你,只是对乙醚的呼喊! – miken32
我不认为它是个人的;)顺便说一句,我觉得你的问题很有趣,我很高兴你有一个很好的答案(我正在研究一个非常类似的方法,但巴尔马速度更快)如果你希望看到我的方法,[这里是SQL小提琴](http://sqlfiddle.com/#!9/1df471/39)。 – Barranka