PostgreSQL中实现跨表求和连接并添加总计行

1. 先对两个表分别按产品聚合求和 #

先计算每个产品在两个表中的汇总数据，得到各自的聚合结果集：

-- 第一个表：聚合金额和客户数
SELECT 
    product,
    SUM(amount) AS total_amount,
    SUM(no_of_customers) AS total_customers
FROM table1
GROUP BY product

-- 第二个表：聚合账单数
SELECT 
    product,
    SUM(no_of_bills) AS total_bills
FROM table2
GROUP BY product

2. 连接两个聚合结果集 #

为了兼容多数数据库，这里用LEFT JOIN + UNION的方式确保所有产品（包括只在其中一个表存在的产品）都被保留；若你使用的数据库支持FULL JOIN（如PostgreSQL），可以用更简洁的写法：

兼容多数数据库的写法 #

WITH agg_table1 AS (
    SELECT 
        product,
        SUM(amount) AS total_amount,
        SUM(no_of_customers) AS total_customers
    FROM table1
    GROUP BY product
),
agg_table2 AS (
    SELECT 
        product,
        SUM(no_of_bills) AS total_bills
    FROM table2
    GROUP BY product
)
-- 保留第一个表的所有产品，匹配第二个表的数据
SELECT 
    COALESCE(a.product, b.product) AS product,
    COALESCE(a.total_amount, 0) AS total_amount,
    COALESCE(a.total_customers, 0) AS total_customers,
    COALESCE(b.total_bills, 0) AS total_bills
FROM agg_table1 a
LEFT JOIN agg_table2 b ON a.product = b.product
UNION
-- 补充第二个表独有的产品
SELECT 
    b.product,
    COALESCE(a.total_amount, 0) AS total_amount,
    COALESCE(a.total_customers, 0) AS total_customers,
    COALESCE(b.total_bills, 0) AS total_bills
FROM agg_table1 a
RIGHT JOIN agg_table2 b ON a.product = b.product
WHERE a.product IS NULL

支持FULL JOIN的简洁写法 #

WITH agg_table1 AS (
    SELECT 
        product,
        SUM(amount) AS total_amount,
        SUM(no_of_customers) AS total_customers
    FROM table1
    GROUP BY product
),
agg_table2 AS (
    SELECT 
        product,
        SUM(no_of_bills) AS total_bills
    FROM table2
    GROUP BY product
)
SELECT 
    COALESCE(a.product, b.product) AS product,
    COALESCE(a.total_amount, 0) AS total_amount,
    COALESCE(a.total_customers, 0) AS total_customers,
    COALESCE(b.total_bills, 0) AS total_bills
FROM agg_table1 a
FULL JOIN agg_table2 b ON a.product = b.product

3. 添加总计行汇总所有品类 #

通过UNION ALL将各产品数据和总计行合并，并用排序逻辑让总计行固定在最后：

WITH agg_table1 AS (
    SELECT 
        product,
        SUM(amount) AS total_amount,
        SUM(no_of_customers) AS total_customers
    FROM table1
    GROUP BY product
),
agg_table2 AS (
    SELECT 
        product,
        SUM(no_of_bills) AS total_bills
    FROM table2
    GROUP BY product
),
joined_result AS (
    SELECT 
        COALESCE(a.product, b.product) AS product,
        COALESCE(a.total_amount, 0) AS total_amount,
        COALESCE(a.total_customers, 0) AS total_customers,
        COALESCE(b.total_bills, 0) AS total_bills
    FROM agg_table1 a
    FULL JOIN agg_table2 b ON a.product = b.product
)
-- 输出各产品明细
SELECT * FROM joined_result
UNION ALL
-- 输出总计行
SELECT 
    '总计' AS product,
    SUM(total_amount) AS total_amount,
    SUM(total_customers) AS total_customers,
    SUM(total_bills) AS total_bills
FROM joined_result
ORDER BY 
    CASE WHEN product = '总计' THEN 1 ELSE 0 END, -- 总计行排最后
    product -- 其他产品按名称排序，可按需调整

关键细节说明 #

• 用COALESCE处理NULL值，确保仅在单表存在的产品，对应字段显示0而非NULL
• WITH子句（CTE）让逻辑分层更清晰，也可以用嵌套子查询替代
• 排序规则可根据实际需求修改，比如按金额降序排列产品

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