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

日期:2019-10-16编辑作者:北京pk10官网-数据库

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

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

以上

本文由北京pk赛车官网下载发布于北京pk10官网-数据库,转载请注明出处:3.自增列和id的差值 相同即连续

关键词:

mysqlimport(数据导入工具)后续会在讲到

一.  perror 错误代码查看工具           在mysql的使用过程中,可能会出现各种各样的error。这些error有些是由于操...

详细>>

28_数据库_第28天(数据库、表及表数据、SQL语句

今日内容介绍 1.MySQL数据库 2.SQL语句 01数据库概念 A: 什么是数据库 数据库就是存储数据的仓库,其本质是一个文件系...

详细>>

右键点击数据库-》点击新建数据库-》输入数据库

一、数据库定义 数据库(Database)是依据数据结构来组织、存款和储蓄和保管数据的库房。数据库的操作分为二种情势:...

详细>>

sqlserver 跨服务器备份表

exec sp_configure 'show advanced options',1 reconfigure exec sp_configure 'Ad Hoc Distributed Queries',1 reconfigure SELECT * into T_System_Organizatio...

详细>>