leetcode-database-180. Consecutive Numbers
来源:互联网 发布:绝不知乐在何处的绝 编辑:程序博客网 时间:2024/06/15 13:21
Write a SQL query to find all numbers that appear at least three times consecutively.
+----+-----+| Id | Num |+----+-----+| 1 | 1 || 2 | 1 || 3 | 1 || 4 | 2 || 5 | 1 || 6 | 2 || 7 | 2 |+----+-----+
For example, given the above
Logs
table, 1
is the only number that appears consecutively for at least three times.题意:查询出连续出现3次的数字
思路:
定义参数preNum记录前一个Number的值,curRank记录目前连续出现的次数
@curRank:=@curRank +if(@preNum=Num,0,1)
如果当前的Number值等于preNum,那么curRank+1
select Num,@curRank:=@curRank + if(@preNum=Num,0,1) As rank,@preNum:=Num from Logs s,(select @curRank:=0) r,(select @preNum:=NULL) p order by id ASC
+------+-------+
| Num | rank |
+------+-------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 1 | 3 |
| 2 | 4 |
| 2 | 4 |
+------+-------+
| Num | rank |
+------+-------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 1 | 3 |
| 2 | 4 |
| 2 | 4 |
+------+-------+
select Num,count(rank) as cnt
from(select Num,@curRank:=@curRank + if(@preNum=Num,0,1) As rank,@preNum:=Num from Logs s,(select @curRank:=0) r,(select @preNum:=NULL) p order by id ASC) t
group by rank
+------+-------+
| Num | cnt |
+------+-------+
| 1 | 3 |
| 2 | 1 |
| 1 | 1 |
| 2 | 2 |
+------+-------+
| Num | cnt |
+------+-------+
| 1 | 3 |
| 2 | 1 |
| 1 | 1 |
| 2 | 2 |
+------+-------+
最终sql语句:
select distinct Num as ConsecutiveNums from (select Num,count(rank) as cnt
from(select Num,@curRank:=@curRank + if(@preNum=Num,0,1) As rank,@preNum:=Num from Logs s,(select @curRank:=0) r,(select @preNum:=NULL) p order by id ASC) t
group by rank having cnt>=3) n;
0 0
- leetcode-database-180. Consecutive Numbers
- 【leetcode Database】180. Consecutive Numbers
- [LeetCode][Database]Consecutive Numbers
- [Leetcode] [Database] Consecutive Numbers
- leetcode 180. Consecutive Numbers
- leetcode 180. Consecutive Numbers
- Leetcode 180. Consecutive Numbers
- LEETCODE SQL题目 180. Consecutive Numbers
- leetcode 180. Consecutive Numbers 解题思路
- LeetCode | Consecutive Numbers
- [leetcode] Consecutive Numbers
- LeetCode:Consecutive Numbers
- Leetcode:Consecutive Numbers
- [LeetCode] Consecutive Numbers - SQL
- Leetcode: Consecutive Numbers
- LeetCode OJ Consecutive Numbers
- 【Leetcode】Consecutive Numbers
- leetcode-Consecutive numbers
- Android_SQLite数据库存储
- Android 官方文档导读(Introduction)
- OPENGL纹理
- WebDriverException: Message: Unexpected error launching Internet Explorer
- 堆排序
- leetcode-database-180. Consecutive Numbers
- node 获取本地IP地址
- 【经典算法】:BFS与DFS
- 排序--归并排序
- 动态规划问题实例讲解
- jQuery获取表格中一行的值和获取重复ID的值
- Android知识梳理之屏幕适配全攻略
- 负载均衡调度算法大全
- HTMLTestRunner输出是乱码