目录
1.连续出现的数字(180)
表:Logs
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id 是这个表的主键。
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
示例 1:
输入: Logs 表: +----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ 输出: Result 表: +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+ 解释:1 是唯一连续出现至少三次的数字。
解法一(when case 加自定义变量)
首先分为两部分,第一部分是给每个num加一个count,第二部分是去重。
这里用了两个自定义变量@pre和@count,如果自定义变量后直接加=就是判断,如果是:=就是赋值。这里还用了case when语句,也就是类似于代码中的if else。用法:
CASE WHEN condition THEN result
[WHEN...THEN...]
ELSE result
END
具体实现代码如下:
select distinct Num ConsecutiveNums
from(select Num ,
case
when @pre=Num then @count:=@count+1
when (@pre:=Num) is not null then @count:=1
end as count
from logs,(select @pre:=null,@count:=null) d
) t
where t.count>=3
解法二(特殊思路)
在力扣解法中看到这种解法,感觉很新奇,没想到还可以这样写,效率和上面的差不多。直接一次筛选出相邻且相同数字的三个,然后去重即可。
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
解法三(lag,lead函数)
首先普及一下知识,Lag/Lead(col,n,DEFAULT) 用于统计窗口内当前行往前或者往后第n行值
- 第一个参数为列名,
- 第二个参数为往后/前第n行(可选,默认为1),
- 第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
需要注意的是lag 取得是当前行之前的数据,lead 取的实当前行之后的数据
select
distinct t.num as ConsecutiveNums
from
(
select
num,
lag(num, 1) over(order by id) as num1,
lag(num, 2) over(order by id) as num2
from Logs
) t
where t.num = t.num1 and t.num1 = t.num2
select
distinct t.num as ConsecutiveNums
from
(
select
num,
lag(num, 1) over(order by id) as num1,
lead(num, 1) over(order by id) as num2
from Logs
) t
where t.num = t.num1 and t.num1 = t.num2
解法四(row_number函数
)
MySQL中,row_number()
函数 用来分区的,使用它 为返回的结果集中的每一行 生成一个序列号
(为行分配序号),第一个数字以1开头。
下面也是力扣的同学写的,我简要说明一下思路,首先也是分为两部分,第一部分查出数据,第二部分负责筛选去重,rn是给按id排序加一下序号,作者应该是考虑到id不连续的情况。id_rn是通过num分区,并按id排序给出序号。然后在去重的过程中,根据num和rn-id_rn排序,这里的第二个排序又可能不太好看出,因为上面的rn和id_rn都是根据id分组的,所以如果相减的话,他们也是同步的,而我们的num也不会重复,所以这样分组出来的数据是正确的。
select
distinct t.num as ConsecutiveNums
from
(
select
id,
num,
row_number() over(order by id) as rn,
row_number() over(partition by num order by id) as id_rn
from Logs
) t
group by t.num, (t.rn - t.id_rn)
having count(*) >= 3