SQL实现为Reasons列新增'rest'类别并计算填充对应数值

问题背景 #

现有临时表reasons的创建及数据插入代码如下：

CREATE TEMP TABLE reasons
(
Reasons INT ,
Counts_students VARCHAR(50) ,
total_count int
);

INSERT INTO reasons
VALUES ( 'A',2,null),
( 'B',3,null),
('C',8,null),
('D',1,null),
('E',2,null),
('F',5,null),
(NULL,NULL,45)

需求：在查询结果的Reasons类别中新增一行'rest'，其对应的Counts_students值等于total_count（45）减去所有非空Counts_students的总和。

解决方案 #

方法一：直接用UNION ALL拼接结果 #

-- 获取原有非空Reasons的有效数据行
SELECT Reasons, Counts_students
FROM reasons
WHERE Reasons IS NOT NULL
UNION ALL
-- 计算并生成'rest'行
SELECT 'rest' AS Reasons,
       (SELECT total_count FROM reasons WHERE total_count IS NOT NULL) - SUM(CAST(Counts_students AS INT)) AS Counts_students
FROM reasons
WHERE Reasons IS NOT NULL;

方法二：用CTE预计算汇总值（可读性更强） #

WITH summary_stats AS (
    -- 提前计算非空Counts_students的总和与总人数
    SELECT 
        SUM(CAST(Counts_students AS INT)) AS sum_students,
        MAX(total_count) AS total_students
    FROM reasons
)
-- 合并原有数据与新增的'rest'行
SELECT Reasons, Counts_students
FROM reasons
WHERE Reasons IS NOT NULL
UNION ALL
SELECT 'rest' AS Reasons, (total_students - sum_students) AS Counts_students
FROM summary_stats;

关键说明 #

• 由于Counts_students字段类型为VARCHAR(50)，计算时必须用CAST转换为整数类型，避免字符串运算错误
• 两种方法均通过UNION ALL合并原有数据与新增行，保证结果完整性
• 若临时表中total_count仅存在唯一非空值，用MAX函数或直接子查询均可正确获取该值

内容的提问来源于stack exchange，提问作者user2458552