2019年11月27日
目录
题目:180. 连续出现的数字
解题1:利用用户变量实现对连续出现的值进行计数
select distinct Num as ConsecutiveNums
from (
select Num,
case
when @prev = Num then @count := @count + 1
when (@prev := Num) is not null then @count := 1
end as CNT
from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3;
- 使用中间表(一列是数字,一列是递增次数,使用case .. end .. )
SELECT
Num, case
when @prev = Num then @count := @count + 1
when (@prev := Num) is not null then @count := 1
end as CNT
FROM `logs`,(SELECT @prev := null, @count := null) as t
;
- distinct 过滤掉Num列
解题2:使用自连接解决问题
SELECT DISTINCT a.Num as ConsecutiveNums FROM `logs` as a , `logs` as b, `logs` as c
WHERE a.id = b.Id - 1 and b.Id = c.Id - 1 and a.Num = b.Num and b.Num = c.Num;
- 利用“自连接(自身连接)“的思路,自连接(自身连接)的本质是把一张表复制出多张一模一样的表来使用,显而易见,效率较低
- 假如需要统计连续出现n次,岂不是要自连接n次?