表中数据结构如下
user_id date1 2015-06-01
2 2015-06-01
2 2015-06-02
2 2015-06-18
2 2015-07-12
2 2015-07-13
2 2015-07-13
2 2015-07-13
2 2015-07-14
2 2015-07-15
3 2015-07-10
3 2015-07-14
3 2015-07-15
3 2015-07-15
3 2015-07-15
3 2015-07-15
4 2015-07-13怎么查询出当前连续签到天数,比如user_id =2 的已经连续签到4天,最后连续签到日期为2015-07-12,2015-07-13,2015-07-14,2015-07-15user_id =3 已经连续签到2天,最后连续签到日期为2015-07-14,2015-07-15
user_id =4 没有签到,连续签到天数为0最后要得到的是
用户ID,连续天数
1 0
2 4
3 2
4 0要将查询的结果更新到到另一个表中,最好能高效点,谢谢各位!
user_id date1 2015-06-01
2 2015-06-01
2 2015-06-02
2 2015-06-18
2 2015-07-12
2 2015-07-13
2 2015-07-13
2 2015-07-13
2 2015-07-14
2 2015-07-15
3 2015-07-10
3 2015-07-14
3 2015-07-15
3 2015-07-15
3 2015-07-15
3 2015-07-15
4 2015-07-13怎么查询出当前连续签到天数,比如user_id =2 的已经连续签到4天,最后连续签到日期为2015-07-12,2015-07-13,2015-07-14,2015-07-15user_id =3 已经连续签到2天,最后连续签到日期为2015-07-14,2015-07-15
user_id =4 没有签到,连续签到天数为0最后要得到的是
用户ID,连续天数
1 0
2 4
3 2
4 0要将查询的结果更新到到另一个表中,最好能高效点,谢谢各位!
LEFT JOIN
(SELECT user_id,SUM(DATEDIFF(`date`,ss)+1) AS ss FROM (
SELECT DISTINCT a.`user_id`,a.`date`,(SELECT MAX(`date`) FROM ttq2 WHERE
a.user_id=user_id AND
( DATE(a.`date`)=DATE(`date`) + INTERVAL 1 DAY
OR
DATE(a.`date`)=DATE(`date`) - INTERVAL 1 DAY)
) AS ss FROM ttq2 a) b WHERE ss IS NOT NULL
AND `date`>ss
GROUP BY user_id) b
ON a.`user_id`=b.`user_id`
GROUP BY a.`user_id`
用SQL语句来实现时效率非常差,需要多次JOIN。