去除Mysql中日期时间的重叠 [英] Removal of overlapping of datetime in Mysql
本文介绍了去除Mysql中日期时间的重叠的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表,用于存储登录和注销时间.帮我去除行之间日期时间的重叠
I have a table which stores login and logout time. Help me with removal of overlapping of datetime between the rows
我有这样的桌子
id | login_time | logout_time
1 |'2017-02-20 11:20:00' | '2017-02-20 12:10:00'
2 |'2017-02-20 12:13:00' | '2017-02-20 12:40:00'
3 |'2017-02-20 12:30:00' | '2017-02-20 13:10:00'
4 |'2017-02-20 13:20:00' | '2017-02-20 13:30:00'
5 |'2017-02-20 13:25:00' | '2017-02-20 14:20:00'
输出应该像
login_time | logout_time
2017-02-20 11:20:00 | 2017-02-20 12:10:00
2017-02-20 12:13:00 | 2017-02-20 13:10:00
2017-02-20 13:20:00 | 2017-02-20 14:20:00
帮帮我.
推荐答案
根据您的输出示例,我更改了查询并检查了它是否返回了您预期的结果集
According to your output sample I changed my query and checked it returns your expected result set
select t1.logintime,t2.logoutime from
(
select F1.logintime,F1.rank from
(
select
t.logintime,
CASE WHEN @prevRank = logintime THEN @currRank
WHEN @prevRank := logintime THEN @currRank := @currRank + 1
END AS rank
from (
select min(login_time) as logintime from time_table
group by hour(login_time)
) t
cross join (SELECT @currRank := 0, @prevRank := NULL) r
) as F1
) t1
inner join
(
select F2.logoutime,F2.rank from
(
select
t.logoutime,
CASE WHEN @preRank = logoutime THEN @curRank
WHEN @preRank := logoutime THEN @curRank := @curRank + 1
END AS rank
from (
select min(logout_time) as logoutime from time_table
group by hour(logout_time)
) t
cross join (SELECT @curRank := 0, @preRank := NULL) r
) F2
) as t2
on t1.rank = t2.rank
这篇关于去除Mysql中日期时间的重叠的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文