目录
前言
问题描述
题目连接点击跳转,请自行阅读。
本题我采用的方法为,使用row_number得到的排名与原始序号作差,然后求分组数量即可。
但过程中会产生BIGINT UNSIGNED value is out of range in的错误,于是为此查了一些资料。
题目代码
select
distinct num as ConsecutiveNums
from(
select
id,
num,
row_number()over(partition by num order by id) as rn
from Logs
)as res
group by num,id-cast(rn as signed)
having count(1)>=3
问题分析
原始代码为
select
distinct num as ConsecutiveNums
from(
select
id,
num,
row_number()over(partition by num order by id) as rn
from Logs
)as res
group by num,id-rn -- 正确的此行为: group by num,id-cast(rn as signed)
having count(1)>=3
报错:
BIGINT UNSIGNED value is out of range in '(
res.
id-
res.
rn)'
报错原因
id-rn
超出范围了,由于id本身为int
类型,但row_number得到的rn
则为unsigned int,所以两者计算时,会将int转换为unsigned int,而无符号数之间的减法和正常的不太一样,所以,需要将无符号数转换成int
。