当前位置: 萬仟网 > IT编程>数据库>Mysql > LeetCode——Consecutive Numbers

LeetCode——Consecutive Numbers

2019年10月09日 17:51  | 萬仟网IT编程  | 我要评论
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               |
+-----------------+

题意:求表中连续出现3次以上的数据.

因此,根据题意构造第一版本答案(使用连续的id进行比较):

# write your mysql query statement below
select distinct t1.num as consecutivenums 
    from 
        logs t1,
        logs t2,
        logs t3
    where 
        t1.id = t3.id - 1
        and t2.id = t3.id + 1
        and t1.num = t2.num
        and t2.num = t3.num;

当前版本答案通过了测试,但是运行效率太低了.
分析原因,可能与t1.id = t3.id - 1条件相关,当t3.id为0时,-1不会寻找到相关数据,导致sql执行缓慢.
因此,修改为如下所示:

# write your mysql query statement below
# write your mysql query statement below
select distinct t1.num as consecutivenums 
    from 
        logs t1,
        logs t2,
        logs t3
    where 
        t2.id = t1.id + 1
        and t3.id = t1.id + 2
        and t1.num = t2.num
        and t2.num = t3.num;

此版本,效率得到了巨大的提高。

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com