在网上看到一道有意思的题目，大意是如何在mysql查询连续的时间内登录的次数。原文链接：
http://www.oschina.net/question/573517_118821
首先建表，填充测试数据：
CREATE TABLE tmysql_test_lianxu_3 (
id int(11) NOT NULL AUTO_INCREMENT,
uid int(11) DEFAULT NULL,
sts datetime DEFAULT NULL COMMENT '登录时间',
ets datetime DEFAULT NULL COMMENT '离线时间',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
测试数据为：
INSERT INTO tmysql_test_lianxu_3 VALUES (1, 1, '2014-1-1 21:00:00', '2014-1-2 07:00:00');
INSERT INTO tmysql_test_lianxu_3 VALUES (2, 1, '2014-1-2 15:37:57', '2014-1-2 21:00:00');
INSERT INTO tmysql_test_lianxu_3 VALUES (3, 2, '2014-1-1 09:00:00', '2014-1-1 15:00:00');
INSERT INTO tmysql_test_lianxu_3 VALUES (4, 2, '2014-1-2 09:00:00', '2014-2-1 16:00:00');
INSERT INTO tmysql_test_lianxu_3 VALUES (5, 1, '2014-1-4 10:00:00', '2014-1-4 18:00:00');
INSERT INTO tmysql_test_lianxu_3 VALUES (6, 1, '2014-1-5 12:00:00', '2014-1-5 13:00:00');
INSERT INTO tmysql_test_lianxu_3 VALUES (7, 2, '2014-1-10 00:00:00', '2014-1-10 06:00:00');
INSERT INTO tmysql_test_lianxu_3 VALUES (8, 2, '2014-1-11 13:00:00', '2014-1-11 18:00:00');
INSERT INTO tmysql_test_lianxu_3 VALUES (10, 2, '2014-1-12 12:00:00', '2014-1-12 18:00:00');
INSERT INTO tmysql_test_lianxu_3 VALUES (11, 1, '2014-1-8 06:00:00', '2014-1-8 16:00:00');
INSERT INTO tmysql_test_lianxu_3 VALUES (12, 2, '2014-1-11 21:00:00', '2014-1-12 06:00:00');
在Oracle中可以使用row_number搞定，mysql中怎么做呢？
可以参考链接：
http://www.explodybits.com/2011/11/mysql-row-number/
首先看原文中给出的答案：
SELECT uid, days, COUNT(*) AS num
FROM (SELECT uid,
@cont_day :=
(CASE
WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt) = 1) THEN
(@cont_day + 1)
ELSE
1
END) AS days,
(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
@last_uid := uid,
@last_dt := login_dt
FROM (SELECT uid, DATE(sts) AS login_dt
FROM tmysql_test_lianxu_3
ORDER BY uid, sts) AS t,
(SELECT @last_uid := '',
@last_dt := '',
@cont_ix := 0,
@cont_day := 0) AS t1) AS t2
GROUP BY uid, days;
也是使用了mysql模拟oracle的row_number函数。
运行结果是:
我看了半天发现结果好像不是我想要的，我想要的是要有开始时间，结束时间之类的。
看下中间表再说：
SELECT uid,
@cont_day :=
(CASE
WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)=1) THEN
(@cont_day + 1)
ELSE
1
END) AS days,
(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
@last_uid := uid,
@last_dt := login_dt login_day
FROM (SELECT uid, DATE(sts) AS login_dt
FROM tmysql_test_lianxu_3
ORDER BY uid, sts) AS t,
(SELECT @last_uid := '',
@last_dt := '',
@cont_ix := 0,
@cont_day := 0) AS t1
结果为：
看了下可以这么做，连续日期去最大的days,开始时间，结束时间去login_day，而是这样写了：
SELECT uid, max(days) lianxu_days, min(login_day) start_date,max(login_day) end_date
FROM (SELECT uid,
@cont_day :=
(CASE
WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)=1) THEN
(@cont_day + 1)
ELSE
1
END) AS days,
(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
@last_uid := uid,
@last_dt := login_dt login_day
FROM (SELECT uid, DATE(sts) AS login_dt
FROM tmysql_test_lianxu_3
ORDER BY uid, sts) AS t,
(SELECT @last_uid := '',
@last_dt := '',
@cont_ix := 0,
@cont_day := 0) AS t1) AS t2
GROUP BY uid, cont_ix;
结果是：
这里存在的问题是：表里面的的sts登录时间不能有2条uid相同时间在同一天内。
解决方法是:在case中添加一个<1 的判断条件
SELECT uid, max(days) lianxu_days, min(login_day) start_date,max(login_day) end_date
FROM (SELECT uid,
@cont_day :=
(CASE
WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)=1) THEN
(@cont_day + 1)
WHEN (@last_uid = uid AND DATEDIFF(login_dt, @last_dt)<1) THEN
(@cont_day + 0)
ELSE
1
END) AS days,
(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
@last_uid := uid,
@last_dt := login_dt login_day
FROM (SELECT uid, DATE(sts) AS login_dt
FROM tmysql_test_lianxu_3
ORDER BY uid, sts) AS t,
(SELECT @last_uid := '',
@last_dt := '',
@cont_ix := 0,
@cont_day := 0) AS t1) AS t2
GROUP BY uid, cont_ix;
存在的问题：
时间sts的时分秒不见了。
欢迎各位留下更好的查询SQL,如本文中的SQL有问题也请指出，谢谢。
全文完。
————————————————
版权声明：本文为CSDN博主「nydia_lvhq」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/nydia_lvhq/article/details/49926557
网友评论