编写一个 SQL 查询，查找所有数字最大连续次数，以及对应的最小id和最大id。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------------------------+
| Num | Continuation | Min | Max|
+-----------------------------+
| 1 | 3 | 1 | 3|
+-----------------------------+
| 2 | 2| 6 | 7|
+---------------------------------+
这一题我只想到这样解，求大佬教教我怎么做呜呜。
select Num,
max(Cnt) as Continuation
from (select Id,Num,
row_number() over() as Cnt
from Logs) as temp
group by Num
;
max(Cnt) as Continuation
from (select Id,Num,
row_number() over() as Cnt
from Logs) as temp
group by Num
;
全部评论
(1) 回帖