将两个复杂SQL查询合并为单查询的技术需求

方法一：使用FULL OUTER JOIN（支持该语法的数据库：PostgreSQL、SQL Server等） #

通过全外连接保留两个查询中所有的nama记录，再用COALESCE填充空值：

SELECT 
    COALESCE(Q1.nama, Q2.nama) AS nama,
    COALESCE(Q1.六月库存结余, 0) AS 六月库存结余,
    COALESCE(Q1.七月入库量, 0) AS 七月入库量,
    COALESCE(Q1.总计, 0) AS 总计,
    COALESCE(Q2.七月出库量, 0) AS 七月出库量,
    COALESCE(Q2.七月库存结余, COALESCE(Q1.总计, 0)) AS 七月库存结余
FROM (
    -- 第一个查询：计算6月库存结余、7月入库量、总计
    SELECT 
        nama,
        SUM(CASE WHEN month = 6 THEN balance END) AS 六月库存结余,
        SUM(CASE WHEN month = 7 THEN in_qty END) AS 七月入库量,
        SUM(CASE WHEN month = 6 THEN balance END) + SUM(CASE WHEN month = 7 THEN in_qty END) AS 总计
    FROM inventory
    GROUP BY nama
) Q1
FULL OUTER JOIN (
    -- 第二个查询：计算7月出库量、7月库存结余
    SELECT 
        nama,
        SUM(out_qty) AS 七月出库量,
        (SELECT balance FROM inventory WHERE nama = i.nama AND month = 6) + SUM(in_qty) - SUM(out_qty) AS 七月库存结余
    FROM inventory i
    WHERE month = 7
    GROUP BY nama
) Q2 ON Q1.nama = Q2.nama
ORDER BY nama;

方法二：使用UNION ALL + GROUP BY（通用型，适配MySQL等不支持全外连接的数据库） #

先将两个查询的结果合并，再按nama聚合，确保所有记录都被保留：

SELECT 
    nama,
    SUM(六月库存结余) AS 六月库存结余,
    SUM(七月入库量) AS 七月入库量,
    SUM(总计) AS 总计,
    SUM(七月出库量) AS 七月出库量,
    CASE 
        WHEN SUM(七月库存结余) IS NOT NULL THEN SUM(七月库存结余)
        ELSE SUM(总计)
    END AS 七月库存结余
FROM (
    -- 第一个查询结果
    SELECT 
        nama,
        SUM(CASE WHEN month = 6 THEN balance END) AS 六月库存结余,
        SUM(CASE WHEN month = 7 THEN in_qty END) AS 七月入库量,
        SUM(CASE WHEN month = 6 THEN balance END) + SUM(CASE WHEN month = 7 THEN in_qty END) AS 总计,
        0 AS 七月出库量,
        NULL AS 七月库存结余
    FROM inventory
    GROUP BY nama

    UNION ALL

    -- 第二个查询结果
    SELECT 
        nama,
        0 AS 六月库存结余,
        0 AS 七月入库量,
        0 AS 总计,
        SUM(out_qty) AS 七月出库量,
        (SELECT balance FROM inventory WHERE nama = i.nama AND month = 6) + SUM(in_qty) - SUM(out_qty) AS 七月库存结余
    FROM inventory i
    WHERE month = 7
    GROUP BY nama
) combined
GROUP BY nama
ORDER BY nama;

关键说明 #

• 用COALESCE或SUM聚合时的默认值处理空值，避免结果出现NULL
• 全外连接和UNION ALL两种方式都能保留所有nama，不管是否在两个查询中都存在
• 七月库存结余的计算需兼容只有入库或只有出库的场景，确保逻辑正确

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