180. Consecutive Numbers

男娘i 2022-04-24 15:06 300阅读 0赞 
  1. Write a SQL query to find all numbers that appear at least three times consecutively.
  3. +----+-----+
  4. | Id | Num |
  5. +----+-----+
  6. | 1  |  1  |
  7. | 2  |  1  |
  8. | 3  |  1  |
  9. | 4  |  2  |
  10. | 5  |  1  |
  11. | 6  |  2  |
  12. | 7  |  2  |
  13. +----+-----+
  14. For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.
  16. +-----------------+
  17. | ConsecutiveNums |
  18. +-----------------+
  19. | 1               |
  20. +-----------------+

本题也是对join使用的考察。与175. Combine Two Tables类似。join的用法在175. Combine Two Tables中有记录。

  1. select 
  2. distinct l1.num as ConsecutiveNums
  3. from logs l1 
  4. inner join logs l2 
  5. on l1.ID+1 = l2.ID 
  6. inner join logs l3 
  7. on l2.ID+1 = l3.ID
  8. where l1.num = l2.num and l1.num = l3.num

发表评论

表情：
评论列表 （有 0 条评论，300人围观）

还没有评论，来说两句吧...

相关阅读