Consecutive Numbers

最新推荐文章于 2022-06-03 22:08:50 发布

havedream_one

最新推荐文章于 2022-06-03 22:08:50 发布

阅读量2.5k

点赞数

分类专栏： mysql 文章标签： mysql leetcode

本文链接：https://blog.csdn.net/havedream_one/article/details/45419937

版权

mysql 专栏收录该内容

29 篇文章 0 订阅

订阅专栏

    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.
    解法一：
    参考[Rank Scores](http://blog.csdn.net/havedream_one/article/details/45395063)
    从上往下对齐排名，如果相等，则排名相等，不相等则排名加一
    代码如下：
    select num,@curRank := @curRank+IF(@preNum = num,0,1) as rank ,@preNum:=num from logs s,(select @curRank := 0) r,(select @preNum := null) p;
    +------+------+--------------+
    | num  | rank | @preNum:=num |
    +------+------+--------------+
    |    1 |    1 |            1 |
    |    1 |    1 |            1 |
    |    1 |    1 |            1 |
    |    2 |    2 |            2 |
    |    1 |    3 |            1 |
    |    2 |    4 |            2 |
    |    2 |    4 |            2 |
    +------+------+--------------+
    如上所示，如果一个num的连续排名超过3则符合题意。
    select num,count(rank) as count 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 ) t 
        group by rank;
    +------+-------+
    | num  | count |
    +------+-------+
    |    1 |     3 |
    |    2 |     1 |
    |    1 |     1 |
    |    2 |     2 |
    +------+-------+
    得到上述的表之后，再使用having条件选择。
    最终结果：
    select distinct num from(
        select num,count(rank) as count 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 ) t 
            group by rank having count >= 3;
        ) tmp;
    解法二：
    1、从第一条记录搜索，前后相同，count++，不相等，count=1;
    2、判断，若rank大于3则符合题意
    select distinct num 
        from ( 
            select num,@curRank := @preRank+IF(@preNum = num,1,0),@preRank :=IF(@preNum = num,@curRank,1) as rank,@preNum := num 
                from Logs s,(select @preRank := 1) r,(select @preNum := null) p
        ) t 
        where rank >= 3;