MySQL查询显示连续的结果
2024-09-04 12:28:02
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);
听说还可以用存储过程来完成,不过我没尝试,稍后尝试
以上
最新文章
- Backtracking algorithm: rat in maze
- php工作笔记7-概率算法
- ASP.NET SignalR 与 LayIM2.0 配合轻松实现Web聊天室(三) 之 实现单聊,群聊,发送图片,文件。
- Remainder
- hdu 2061
- Ext 4.2以后版本 ComboBox 联动
- foreach的指针问题
- Freebsd 下如何最有效率的安装软件
- CSS3超酷移动手机滑动隐藏側边栏菜单特效
- Delphi中一些常用的组合键值
- SE 2014年4月14日
- hdu 动态规划(46道题目)倾情奉献~ 【只提供思路与状态转移方程】(转)
- sqlalchemy操作
- 开源免费的.NET图像即时处理的组件ImageProcessor
- 【转】Kali更新源
- Linux下vsftpd的安装,Java上传文件实现。
- HDU4403-模拟、数学
- GBDT
- asp.net --- Menu控件\CSS 和样式
- servlet常用操作
热门文章
- jsf+ejb
- MySQL表完整性约束
- [Java]HashMap实现与哈希冲突,与HashTable的区别
- DotNetAnywhere
- js和jq中常见的各种位置距离之offset()和position()的区别(二)
- SpringBoot---Web开发---SSL配置
- nginx的配置文件server_name的意义 location意义
- 【Java】 Maven多模块项目上传到Sonar扫描问题合集
- springBoot jpa 表单关联查询
- 告别CMD.windows终端神器conemu设置