您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > 3.自增列和id的差值 相同即连续

3.自增列和id的差值 相同即连续

发布时间:2019-09-17 21:54编辑:威尼斯官方网站登录浏览(71)

    #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);
    

    听说还可以用存储过程来完成,不过我没尝试,稍后尝试

    以上

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:3.自增列和id的差值 相同即连续

    关键词:

上一篇:威尼斯官方网站末段理解运用那些工具

下一篇:没有了