对比结构相同的两张SQL表Revenue字段：JOIN语句报错求助

错误原因 #

• SQL语法错误：GROUP BY子句位置错误，JOIN必须紧跟在表引用之后，GROUP BY需放在所有表关联逻辑完成后
• 关联条件不完整：仅通过Company关联无法确保是同一公司的同一产品，需同时关联Company和Product
• 逻辑错误：直接在原始表上关联后汇总，会导致数据重复计算，无法得到分表的正确汇总结果

修正后的SQL语句（支持CTE的数据库：PostgreSQL、SQL Server、MySQL 8.0+等） #

WITH CommonProducts AS (
    -- 获取两张表中同时存在的公司+产品组合
    SELECT DISTINCT Company, Product FROM GeographyA
    INTERSECT
    SELECT DISTINCT Company, Product FROM GeographyB
),
SummaryA AS (
    -- 汇总GeographyA的收入数据
    SELECT Company, Product, Geography, SUM(Revenue) AS Revenue
    FROM GeographyA
    GROUP BY Company, Product, Geography
),
SummaryB AS (
    -- 汇总GeographyB的收入数据
    SELECT Company, Product, Geography, SUM(Revenue) AS Revenue
    FROM GeographyB
    GROUP BY Company, Product, Geography
)
-- 合并两个汇总结果，仅保留共同的公司+产品
SELECT s.Company, s.Product, s.Revenue, s.Geography
FROM SummaryA s
JOIN CommonProducts cp 
    ON s.Company = cp.Company AND s.Product = cp.Product
UNION ALL
SELECT s.Company, s.Product, s.Revenue, s.Geography
FROM SummaryB s
JOIN CommonProducts cp 
    ON s.Company = cp.Company AND s.Product = cp.Product
ORDER BY Company, Product, Geography;

兼容老版本数据库的写法（无CTE支持） #

SELECT s.Company, s.Product, s.Revenue, s.Geography
FROM (
    SELECT Company, Product, Geography, SUM(Revenue) AS Revenue
    FROM GeographyA
    GROUP BY Company, Product, Geography
) s
JOIN (
    SELECT DISTINCT Company, Product FROM GeographyA
    INTERSECT
    SELECT DISTINCT Company, Product FROM GeographyB
) cp 
    ON s.Company = cp.Company AND s.Product = cp.Product
UNION ALL
SELECT s.Company, s.Product, s.Revenue, s.Geography
FROM (
    SELECT Company, Product, Geography, SUM(Revenue) AS Revenue
    FROM GeographyB
    GROUP BY Company, Product, Geography
) s
JOIN (
    SELECT DISTINCT Company, Product FROM GeographyA
    INTERSECT
    SELECT DISTINCT Company, Product FROM GeographyB
) cp 
    ON s.Company = cp.Company AND s.Product = cp.Product
ORDER BY Company, Product, Geography;

说明 #

1. 先用INTERSECT筛选出两张表都存在的Company+Product组合，确保只对比同时存在的记录
2. 分别对两张表做独立汇总，避免跨表关联导致的计算错误
3. 用UNION ALL合并两个汇总结果，输出你期望的分表行格式
4. 最终按Company、Product、Geography排序，方便对比数据

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