180.Consecutive_Numbers
来源:互联网 发布:淘宝托管公司收费 编辑:程序博客网 时间:2024/06/17 06:02
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.
+-----------------+| ConsecutiveNums |+-----------------+| 1 |+-----------------+
sql脚本
SELECT DISTINCT L1.Num as ConsecutiveNumsFROM Logs L1, Logs L2, Logs L3WHERE L2.Id = L1.Id + 1AND L3.Id = L2.Id + 1AND L1.Num = L2.NumAND L2.Num = L3.Num
也可以使用JOIN子句完成同样的功能:
SELECT DISTINCT L1.Num FROM Logs L1JOIN Logs L2 ON L1.Id + 1 = L2.IdJOIN Logs L3 ON L1.Id + 2 = L3.IdWHERE L1.Num = L2.Num AND L1.Num = L3.NumORDER BY L1.Num
上面两种方法可以用于找到至少三次连续出现的数字,如果将连续出现的数字扩展到N个,按照上面思路写出的SQL语句就会比较长。因此可以用下面这种方式来查询:
SELECT DISTINCT NumFROM ( SELECT Num, CASE WHEN @prev = Num THEN @count := @count + 1 WHEN (@prev := Num) IS NOT NULL THEN @count := 1 END CNT FROM Logs, (SELECT @prev := NULL) X ORDER BY Id) AS AWHERE A.CNT >= 3
将最后一行的3改为N,即可用于查询至少N次连续出现的数字。
阅读全文
0 0
- 180.Consecutive_Numbers
- leetcode 180. Consecutive Numbers
- leetcode 180. Consecutive Numbers
- Leetcode 180. Consecutive Numbers
- 180. Consecutive Numbers
- 180. Consecutive Numbers
- 180. Consecutive Numbers
- 180. Consecutive Numbers
- leetcode-database-180. Consecutive Numbers
- 【leetcode Database】180. Consecutive Numbers
- SGU 180. Inversions 离散 + 树状数组
- LEETCODE SQL题目 180. Consecutive Numbers
- leetcode 180. Consecutive Numbers 解题思路
- 180.n1-科大讯飞语音识别
- SGU 180. Inversions(归并排序求逆序数)
- 【SGU】180. Inversions(归并排序求逆序数)
- 180. Good for good is natural, good for evil is manly.以德报德是常理,以德报怨大丈夫
- sgu 180. Inversions (树状数组+离散化,第一道需要改模板的题目,好题)
- UVa 599
- 如何修改tomcat ipv6 转换为 ipv4
- bean标签属性介绍
- windows batch file : backup the SAM files
- c语言参考书
- 180.Consecutive_Numbers
- PAT 1004 求各层次叶子节点的数量
- git实用操作
- Rviz教程(十一):Rviz in Stereo
- Python中的sorted函数以及operator.itemgetter函数
- [编程题] 分苹果
- linux线程实现机制(下)
- SaaS系统的接口对资源访问范围进行限制
- 组合模式