mysql SQL中的连续数(Leetcode)

lymgl2op 于 12个月前发布在 Mysql

关注(0)|答案(2)|浏览(166)

问题是table：日志

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+

id是该表的主键。编写一个SQL查询，查找至少连续出现三次的所有数字。
我的解决方案如下，Leetcode不接受。请帮我找出错误。

with temp1 as
(select num, 
       id, 
       row_number () over (partition by num order by id) as r
from logs),  
temp2 as
(select (id-r) as rn, num, count(num)  
from temp1
group by rn, num
having count(num)>=3)
select num as ConsecutiveNums
from temp

mysql

来源：https://stackoverflow.com/questions/70101327/consecutive-numbers-in-sql-leetcode

2条答案

按热度按时间

apeeds0o1#

尝试：

with temp as (select num,
  lag(num,1) over (order by id asc)  as preceding_num,
  lead(num,1) over (order by id asc) as succeeding_num
from logs)
select
  distinct num as ConsecutiveNums
from temp
where num = preceding_num
and num = succeeding_num;

演示：https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/174
LAG：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag
LEAD：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lead

赞(0）回复(0）举报 12个月前

o7jaxewo2#

SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1,
     Logs l2,
     Logs l3
WHERE l1.Id + 1 = l2.Id
  AND l2.Id + 1 = l3.Id
  AND l1.Num = l2.Num
  AND l2.Num = l3.Num

赞(0）回复(0）举报 12个月前

我来回答

mysql SQL中的连续数(Leetcode)

2条答案

相关问题

热门标签

最新问答