关于力扣【180. 连续出现的数字】而产生的对于无符号数计算的问题

目录

前言

问题描述

题目连接点击跳转，请自行阅读。
本题我采用的方法为，使用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。