题解 | 满足条件的用户的试卷完成数和题目练习数

写过一版错误答案：

with
    temp as (
        select
            b.tag,
            year(a.start_time) as start_year,
            sum(if(a.submit_time is not null, 1, 0)) as cnt
        from
            exam_record a
            left join examination_info b on a.exam_id = b.exam_id
        where
            month(start_time) < 7
        group by
            1,
            2
    ),
    t1 as (
        select
            *,
            rank() over (
                partition by
                    start_year
                order by
                    cnt desc
            ) as rk
        from
            temp
        where
            cnt > 0
    ),
    t2 as (
        select
            a.tag,
            a.cnt as exam_cnt_20,
            b.cnt as exam_cnt_21,
            concat(round((b.cnt - a.cnt) / a.cnt * 100, 1), '%') as growth_rate,
            a.rk as exam_cnt_rank_20,
            b.rk as exam_cnt_rank_21,
            b.rk - a.rk as rank_delta
        from
            t1 a
            left join t1 b on a.tag = b.tag
            and a.start_year = b.start_year -1
        where
            b.tag is not null
    )
select
    *
from
    t2
order by
    growth_rate desc,
    exam_cnt_rank_21 desc;

报错：

错误信息 "BIGINT UNSIGNED value is out of range in '(b.rk - a.rk)'" 表明在计算 b.rk - a.rk 时出现了超出范围的情况。这通常是因为：

1. rk 列的数据类型：RANK() 函数返回的 rk 列可能是 BIGINT UNSIGNED 类型当 b.rk 为 NULL 时，b.rk - a.rk 会导致问题
2. LEFT JOIN 的特性：LEFT JOIN 会返回左表的所有记录，即使右表没有匹配当右表没有匹配时，b.rk 会是 NULL
3. 负数结果：当 b.rk 小于 a.rk 时，结果会是负数对于 UNSIGNED 类型，负数是无效的

解决方案

WITH
    temp AS (
        SELECT
            b.tag,
            YEAR(a.start_time) AS start_year,
            SUM(IF(a.submit_time IS NOT NULL, 1, 0)) AS cnt
        FROM
            exam_record a
            LEFT JOIN examination_info b ON a.exam_id = b.exam_id
        WHERE
            MONTH(start_time) < 7
        GROUP BY
            1,
            2
    ),
    t1 AS (
        SELECT
            *,
            RANK() OVER (
                PARTITION BY start_year
                ORDER BY cnt DESC
            ) AS rk
        FROM
            temp
        WHERE
            cnt > 0
    ),
    t2 AS (
        SELECT
            a.tag,
            a.cnt AS exam_cnt_20,
            b.cnt AS exam_cnt_21,
            CONCAT(
                ROUND(
                    IF(a.cnt = 0, 0, (b.cnt - a.cnt) / a.cnt * 100), 
                    1
                ), 
                '%'
            ) AS growth_rate,
            a.rk AS exam_cnt_rank_20,
            b.rk AS exam_cnt_rank_21,
            CAST(b.rk AS SIGNED) - CAST(a.rk AS SIGNED) AS rank_delta  -- 转换为有符号整数
        FROM
            t1 a
            LEFT JOIN t1 b ON a.tag = b.tag
            AND a.start_year = b.start_year - 1
        WHERE
            b.tag IS NOT NULL
    )
SELECT
    *
FROM
    t2
ORDER BY
    growth_rate DESC,
    exam_cnt_rank_21 DESC;

修正说明

1. 类型转换：使用 CAST(rk AS SIGNED) 将 BIGINT UNSIGNED 转换为有符号整数这允许计算负数结果，解决范围问题
2. 避免除以零：添加 IF(a.cnt = 0, 0, ...) 防止在 a.cnt 为 0 时出现除以零错误
3. 保留 LEFT JOIN 特性：保留 LEFT JOIN 以显示所有可能的匹配，即使右表没有数据

这些修改应该能解决 "BIGINT UNSIGNED value is out of range" 错误，并确保计算结果的正确性。

修正说明

1. 类型转换：使用 CAST(rk AS SIGNED) 将 BIGINT UNSIGNED 转换为有符号整数这允许计算负数结果，解决范围问题
2. 避免除以零：添加 IF(a.cnt = 0, 0, ...) 防止在 a.cnt 为 0 时出现除以零错误
3. 保留 LEFT JOIN 特性：保留 LEFT JOIN 以显示所有可能的匹配，即使右表没有数据