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

最新推荐文章于 2024-06-12 18:41:43 发布

hehewjt

最新推荐文章于 2024-06-12 18:41:43 发布

阅读量4.1k

点赞数

分类专栏： sql 文章标签： mysql

sql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

参考：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;

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.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;