如何按指定数值规则编写SQL查询生成含计算列的结果表

假设你的源表名为 source_table，以下是适用于大多数支持窗口函数的数据库（如MySQL 8+、PostgreSQL、SQL Server）的查询语句：

WITH numbered_rows AS (
    SELECT 
        "Column A",
        -- 这里的ORDER BY需根据实际行顺序调整，比如用主键或插入时间字段，确保前两行是3、4
        ROW_NUMBER() OVER (ORDER BY "Column A") AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM source_table
),
first_rows_sum AS (
    SELECT SUM("Column A") AS sum_val
    FROM numbered_rows
    WHERE row_num < total_rows
)
SELECT 
    nr."Column A",
    CASE
        WHEN nr.row_num < nr.total_rows THEN nr."Column A"
        ELSE 8 - frs.sum_val
    END AS "Column B"
FROM numbered_rows nr, first_rows_sum frs;

逻辑说明： #

1. numbered_rows 子查询：给每行标记行号，并获取表的总行数，确保能区分出最后一行；
2. first_rows_sum 子查询：计算除最后一行外所有行的Column A总和；
3. 最终查询：用CASE分支判断，非最后一行直接取Column A的值，最后一行用固定值8减去前面所有行的总和，保证Column B的总和为8。

如果你的数据库不支持CTE（如MySQL 5.x），可以改用子查询写法：

SELECT 
    t."Column A",
    CASE
        WHEN t.row_num < t.total_rows THEN t."Column A"
        ELSE 8 - (SELECT SUM("Column A") FROM (
            SELECT "Column A", ROW_NUMBER() OVER (ORDER BY "Column A") AS rn
            FROM source_table
        ) sub WHERE sub.rn < (SELECT COUNT(*) FROM source_table))
    END AS "Column B"
FROM (
    SELECT 
        "Column A",
        ROW_NUMBER() OVER (ORDER BY "Column A") AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM source_table
) t;

注意：ORDER BY "Column A"是用来确定行顺序的关键，如果原始数据的顺序不是按Column A排序，需要替换成能保证前两行是3、4的排序字段（比如表的主键、创建时间等），因为SQL表本身是无序集合，必须显式指定排序规则才能得到预期结果。

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