【MySQL】使用 CAST 函数处理 unsigned 相减错误（BIGINT UNSIGNED value is out of range）

力扣题

1、题目地址

2175. 世界排名的变化

2、模拟表

表：TeamPoints

Column Name	Type
team_id	int
name	varchar
points	int

• team_id 包含唯一值。
• 这张表的每一行均包含了一支国家队的 ID，它所代表的国家，以及它在全球排名中的得分。
• 没有两支队伍代表同一个国家。

表：PointsChange

Column Name	Type
team_id	int
points_change	int

• team_id 包含唯一值。
• 这张表的每一行均包含了一支国家队的 ID 以及它在世界排名中的得分的变化。

分数的变化分以下情况：

• 0：代表分数没有改变
• 正数：代表分数增加
• 负数：代表分数降低

TeamPoints 表中出现的每一个 team_id 均会在这张表中出现。

3、要求

国家队的全球排名是按 降序排列 所有队伍的得分后所得出的排名。
如果两支队伍得分相同，我们将按其名称的 字典顺序 排列以打破平衡。

每支国家队的分数应根据其相应的 points_change 进行更新。

编写解决方案来计算在分数更新后，每个队伍的全球排名的变化。

以 任意顺序 返回结果。

查询结果的格式如下例所示：

示例 1：

输入：
TeamPoints 表：

team_id	name	points
3	Algeria	1431
1	Senegal	2132
2	New Zealand	1402
4	Croatia	1817

PointsChange 表：

team_id	points_change
3	399
2	0
4	13
1	-22

输出：

team_id	name	rank_diff
1	Senegal	0
4	Croatia	-1
3	Algeria	1
2	New Zealand	0

解释：

世界排名如下所示：

team_id	name	points	rank
1	Senegal	2132	1
4	Croatia	1817	2
3	Algeria	1431	3
2	New Zealand	1402	4

在更新分数后，世界排名变为下表：

team_id	name	points	rank
1	Senegal	2110	1
3	Algeria	1830	2
4	Croatia	1830	3
2	New Zealand	1402	4

由于在更新分数后，Algeria 和 Croatia 的得分相同，因此根据字典顺序对它们进行排序。
Senegal 丢失了 22分 但他们的排名没有改变。
Croatia 获得了 13分 但是他们的排名下降了 1 名。
Algeria 获得 399分，排名上升了 1 名。
New Zealand 没有获得或丢失分数，他们的排名也没有发生变化。

4、代码编写

要求分析

首先根据要求查询出未更新前的排名

SELECT *, row_number() over (order by points desc, name) AS `rank`
FROM TeamPoints

| team_id | name        | points | rank |
| ------- | ----------- | ------ | ---- |
| 1       | Senegal     | 2132   | 1    |
| 4       | Croatia     | 1817   | 2    |
| 3       | Algeria     | 1431   | 3    |
| 2       | New Zealand | 1402   | 4    |

其次查询出更新后的排名

SELECT one.team_id, 
	   one.name,
       (one.points + two.points_change) AS points, 
       row_number() over (order by (one.points + two.points_change) desc, one.name) AS `rank`
FROM TeamPoints one
		LEFT JOIN PointsChange two USING(team_id)

| team_id | name        | points | rank |
| ------- | ----------- | ------ | ---- |
| 1       | Senegal     | 2110   | 1    |
| 3       | Algeria     | 1830   | 2    |
| 4       | Croatia     | 1830   | 3    |
| 2       | New Zealand | 1402   | 4    |

最后将两个SQL根据 team_id 进行连表，rank 相减即可（里面需要注意的点就是相减错误问题）

知识点

MySQL 当两个字段想减时，如果其中 一个或两个字段 的类型的 unsigned 无签名类型，
如果 差值 小于 0则会报错（BIGINT UNSIGNED value is out of range）

测试：

大于等于 0 的情况：

select cast(1 as unsigned) - 1
# 输入：0

select 2 - cast(1 as unsigned)
# 输入：1

select cast(2 as unsigned) - cast(1 as unsigned)
# 输入：1

小于 0 的情况：

select cast(1 as unsigned) - 2
# 报错：BIGINT UNSIGNED value is out of range in '(cast(1 as unsigned) - 2)'

select 0 - cast(1 as unsigned)
# 报错：BIGINT UNSIGNED value is out of range in '(0 - cast(1 as unsigned))'

select cast(1 as unsigned) - cast(2 as unsigned)
# 报错：BIGINT UNSIGNED value is out of range in '(cast(1 as unsigned) - cast(2 as unsigned))'

小于 0 的报错处理：

select cast(1 as signed) - 2
# 输出：-1

select 1 - cast(2 as signed)
# 输入：-1

• 在两个值是定值的的情况，且是差值是负数的情况，
  直接使用 select cast(1 as signed) - 2 或 select 1 - cast(2 as signed) 即可
• 如果两个值都不清楚的情况，两边都得使用 cast 对值进行处理再相减（下面代码就是很好的例子）

参考：处理 unsigned 相减错误（BIGINT UNSIGNED value is out of range）

代码

SELECT a.team_id, 
	   a.name, 
	   (CAST(a.`rank` AS SIGNED) - CAST(b.`rank` AS SIGNED)) AS rank_diff
FROM (
    SELECT *, row_number() over (order by points desc, name) AS `rank`
    FROM TeamPoints
) AS a 
LEFT JOIN (
    SELECT one.team_id, 
    	   one.name,
    	   (one.points + two.points_change) AS points, 
    	   row_number() over (order by (one.points + two.points_change) desc, one.name) AS `rank`
    FROM TeamPoints one
    		LEFT JOIN PointsChange two USING(team_id)
) AS b USING(team_id)