彩世界平台-彩世界时时app-彩世界开奖app苹果下载

热门关键词: 彩世界平台,彩世界时时app,彩世界开奖app苹果下载

您的位置:彩世界平台 > 工作委员会 > MySQL查询显示连续的结果

MySQL查询显示连续的结果

发布时间:2019-09-03 22:12编辑:工作委员会浏览(130)

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

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

    以上

       找了一些资料,然后我是用到了MySQL字符串处理中的两个函数concat()和left()

      1、【CONCAT(str1,str2,...)

      返回来自于参数连结的字符串。如果任何参数是NULL, 返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。

      [示例]

      select CONCAT('My', 'S', 'QL');

      -> 'MySQL'

      select CONCAT('My', NULL, 'QL');

      -> NULL

    本文由彩世界平台发布于工作委员会,转载请注明出处:MySQL查询显示连续的结果

    关键词:

上一篇:使用SQL语句清空数据库所有表的数据

下一篇:没有了