编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs
表, 1
是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
解法一:
select distinct a.num 'ConsecutiveNums'
from Logs a,Logs b ,Logs c
where a.Id = b.Id-1 and a.Id = c.Id-2 and a.Num = b.Num and a.Num = c.Num
解法二:
select distinct l1.Num as ConsecutiveNums from Logs l1 inner join Logs l2 on l1.Id=l2.Id-1 inner join Logs l3 on l1.Id=l3.Id-2 where l1.Num=l2.Num and l2.Num=l3.Num;