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

来源：互联网发布：微信域名备案编辑：程序博客网时间：2024/05/17 03:04

参考：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      )XWHERE LESS>=30;

2、查询连续同一个动作大于等于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.useridWHERE u.userid IN (SELECT DISTINCT useridFROM (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      )XWHERE LESS>=30) ORDER BY u.userid DESC;

0 0