查询连续7天的数据sql问题

参考：http://www.dreaminto.com/2012/0312/611.html

下面是两段根据自己需求改造的两段sql，备用
1、查询连续一个动作超过30天的用户id编号的集合

SET @wy=0;
SELECT GROUP_CONCAT(DISTINCT userid)
FROM (SELECT MAX(DATE)-MIN(DATE) LESS,userid
FROM (SELECT DATE-rn diff, userid, DATE, rn
FROM (SELECT @wy:=@wy+1 rn, userid,
DATEDIFF(operateTime,'1971-01-01') DATE,operateTime
FROM (SELECT DATE(operateTime) operateTime, userid FROM ub_log
WHERE operateTime>='2014-04-29 00:00:00' AND
operateTime <'2014-07-28 00:00:00'
AND uri = 'synchrodata'
GROUP BY userid, DATE(operateTime)
ORDER BY userid, DATE(operateTime)
)X
)X
)X
GROUP BY diff,userid
)X
WHERE LESS>=30;

SET @wy=0;
SELECT u.userid 'ID',u.username '用户名称',u.nickname '昵称',u.regip '注册时间',CASE ext.sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '保密' END AS '性别',temp.operatetime '最后使用时间'
FROM ub_user u LEFT JOIN ub_user_ext ext ON u.userid = ext.userid
LEFT JOIN (SELECT * FROM (SELECT
userid ,operatetime FROM ub_log
WHERE uri = 'synchrodata' AND userid IN (
'第一步中查询出来的id的集合替换这里'
) ORDER BY operatetime DESC) t GROUP BY userid ORDER BY userid DESC) temp ON u.userid = temp.userid
WHERE u.userid IN (SELECT DISTINCT userid
FROM (SELECT MAX(DATE)-MIN(DATE) LESS,userid
FROM (SELECT DATE-rn diff, userid, DATE, rn
FROM (SELECT @wy:=@wy+1 rn, userid,
DATEDIFF(operateTime,'1971-01-01') DATE,operateTime
FROM (SELECT DATE(operateTime) operateTime, userid FROM ub_log
WHERE operateTime>='2014-04-29 00:00:00' AND
operateTime <'2014-07-28 00:00:00'
AND uri = 'synchrodata'
GROUP BY userid, DATE(operateTime)
ORDER BY userid, DATE(operateTime)
)X
)X
)X
GROUP BY diff,userid
)X
WHERE LESS>=30) ORDER BY u.userid DESC;

最后

以上就是自信钢笔为你收集整理的查询连续7天的数据sql问题的全部内容，希望文章能够帮你解决查询连续7天的数据sql问题所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。