如何通过SQL基于两个年度分组汇总查询计算出口总额与进口总额的差值？

针对你的需求，有两种高效的实现方式，我分别给你拆解说明：

方法一：使用JOIN连接两个子查询结果 #

把你已有的两个查询作为子表，通过year字段做连接，直接计算出口减进口的差值。这种方式逻辑直观，和你原本的思路衔接非常紧密：

SELECT 
    t1.year,
    (t1.TE - t2.TI) AS TradeBalance
FROM 
    (SELECT year, Sum(amount) AS TE 
     FROM tradeinfo 
     WHERE tradetype = 'Export'  -- 这里用=比LIKE更高效，因为是精确匹配场景
     GROUP BY year) t1
JOIN 
    (SELECT year, Sum(amount) AS TI 
     FROM tradeinfo 
     WHERE tradetype = 'Import'
     GROUP BY year) t2 ON t1.year = t2.year
ORDER BY t1.year;

补充：如果某些年份只有出口或只有进口数据，内连接会过滤掉这些年份。如果需要保留所有年份（哪怕其中一项为0），可以改用FULL OUTER JOIN，并配合COALESCE处理NULL值：

SELECT 
    COALESCE(t1.year, t2.year) AS year,
    COALESCE(t1.TE, 0) - COALESCE(t2.TI, 0) AS TradeBalance
FROM 
    (SELECT year, Sum(amount) AS TE 
     FROM tradeinfo 
     WHERE tradetype = 'Export'
     GROUP BY year) t1
FULL OUTER JOIN 
    (SELECT year, Sum(amount) AS TI 
     FROM tradeinfo 
     WHERE tradetype = 'Import'
     GROUP BY year) t2 ON t1.year = t2.year
ORDER BY year;

方法二：使用条件聚合（更简洁高效） #

不需要拆分两个子查询，直接在一个GROUP BY里通过CASE语句分别计算出口和进口总额，然后求差值。这种方式只需要扫描一次表，性能更优：

SELECT 
    year,
    SUM(CASE WHEN tradetype = 'Export' THEN amount ELSE 0 END) AS TE,
    SUM(CASE WHEN tradetype = 'Import' THEN amount ELSE 0 END) AS TI,
    SUM(CASE WHEN tradetype = 'Export' THEN amount ELSE 0 END) - 
    SUM(CASE WHEN tradetype = 'Import' THEN amount ELSE 0 END) AS TradeBalance
FROM tradeinfo
WHERE tradetype IN ('Export', 'Import')  -- 过滤掉无关的交易类型，减少计算量
GROUP BY year
ORDER BY year;

如果只需要年份和差值列，还可以简化成更巧妙的写法：

SELECT 
    year,
    SUM(CASE WHEN tradetype = 'Export' THEN amount ELSE -amount END) AS TradeBalance
FROM tradeinfo
WHERE tradetype IN ('Export', 'Import')
GROUP BY year
ORDER BY year;

这个简化版的逻辑是：出口金额加正值，进口金额加负值，最终求和结果就是TE-TI，代码更精简。

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