菜单

MySQL查询用户连续签到数

2018年11月16日 - MySQL

#mysql中 对于查询结果就显示n条连续实施之题材#

以领扣上撞的一个问题:求满足条件的连接3行结果的显示

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people;
Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).
For example, the table stadium:
+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

For the sample data above, the output is:
+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

1.先是先进行结果集的查询

select id,date,people from stadium where people>=100;

2.深受查询的结果集增加一个自增列

SELECT @newid:=@newid+1 AS newid,test.* 
FROM(SELECT @newid:=0)r, test WHERE people>100

3.起增列和id的差值 相同便连续

SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100

4.以同的差值 放在同样摆表中,并取出连续数超越3底

select if(count(id)>=3,count_concat(id),null)e from(
SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100)
as d group by cha

5.将上步得到的表和主表 取得所待的

SELECT id,DATE,people FROM test,
(SELECT IF (COUNT(id)>3,GROUP_CONCAT(id),NULL)e 
FROM (SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100)AS d   GROUP BY cha ) AS f 
WHERE f.e IS NOT NULL AND FIND_IN_SET(id,f.e);

传闻还得为此存储过程来好,不过我从未尝试,稍后尝试

以上

select uid
    ,max(days) incessancy_days
    ,min(ot) start_date
    ,max(ot) end_date
from
(
    select uid
        ,@cont_day :=
        (
            case when (@last_uid = uid and DATEDIFF(ot, @last_ot)=1) then
            (@cont_day + 1)
            when (@last_uid = uid and DATEDIFF(ot, @last_ot)<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_ot := ot ot
    from
    (
        select uid
            ,login_date as ot
        from log
        where login_date >= '2017-01-01'
            and login_date < '2017-07-01'
        order by uid ,login_date
    ) as t1,
    (
        select @last_uid := '',
            @last_ot  := '',
            @cont_ix  := 0,
            @cont_day := 0
    ) as t2
) as t
group by uid

相关文章

标签:

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图