当前位置： 移动技术网 > IT编程>数据库>Mysql > MySQL查询显示连续的结果

MySQL查询显示连续的结果

2018年09月05日  | 移动技术网IT编程  | 我要评论

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

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

以上

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网