Who said the Id increments by +1?


  • 2
    K

    There has been posted many solutions assuming that next row should be identified with Id = Id + 1. As there is no explicit reference in the condition for that, I considered it is fair to give a generic case solution. It is built around a view that identifies the next element:

    CREATE VIEW Next AS
        SELECT l2.Id, l2.Num, MIN(l1.Id) as Next
        FROM Logs l1, Logs l2
        WHERE l1.Id > l2.Id
        GROUP BY l2.Id ;
    

    With the original example it'll produce a rather trivial table:

    +------+------+------+
    |  Id  |  Num | Next |
    +------+------+------+
    |   1  |   1  |   2  |
    |   2  |   1  |   3  |
    |   3  |   1  |   4  |
    |   4  |   2  |   5  |
    |   5  |   1  |   6  |
    |   6  |   2  |   7  |
    +------+------+------+
    

    Another view appends a value for the next element:

    CREATE VIEW NextNum AS
        SELECT n.Id, n.Num, n.Next, l.Num as NextNum
        FROM Logs l, Next n
        WHERE l.Id = n.Next ;
    

    And the last query finally

    SELECT DISTINCT n.Num
    FROM NextNum n, NextNum nn
    WHERE nn.Id = n.Next AND n.Num = n.NextNum AND n.NextNum = nn.NextNum ;
    

    Unfortunately, creating a view is denied by the OJ: "CREATE VIEW command denied to user 'student'@'localhost' for table 'Next'".
    Otherwise it seems reasonable solution working on my private MySQL DB just fine.


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.