mysql查询自动连续,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);

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

以上

内容来源于网络如有侵权请私信删除