题目描述:编写一个 SQL 查询,查找所有至少连续出现三次的数字。
例如,给定上面的+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
表,
Logs
是唯一连续出现至少三次的数字。
1
+-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+
解法1。3个表join,Id连续,Num相等,输出的字段distinct一下重命名就可以。
select distinct l1.Num as ConsecutiveNums from Logs l1
left join Logs l2 on l1.Id = l2.Id-1
left join Logs l3 on l1.Id = l3.Id-2
where l1.Num = l2.Num and l1.Num = l3.Num
解法2。和上面的解法一致,但没有用join,是默认join了吗?
select distinct l1.Num as ConsecutiveNums
from
Logs l1,
Logs l2,
Logs l3
where
l1.Id = l2.Id - 1
and l2.Id = l3.Id - 1
and l1.Num = l2.Num
and l2.Num = l3.Num