如何优化多国家销量统计SQL，减少重复表查询以提升效率？

原代码存在大量重复的表连接与子查询，每次CTE都重复关联products、orderdetails、orders、customers四张表，造成严重的性能浪费。以下是两种高效优化方案，仅需一次表关联即可实现需求，同时保证无销量时显示NULL。

方案一：预聚合国家-产品销量后拆分 #

先通过一次表关联获取所有目标国家的产品销量聚合数据，再用条件聚合拆分各国家销量与总销量：

WITH Country_Product_Sales AS (
    SELECT 
        p.productCode,
        c.country,
        SUM(od.quantityOrdered) AS sales_quantity
    FROM products p
    LEFT JOIN orderdetails od ON p.productCode = od.productCode
    LEFT JOIN orders o ON od.orderNumber = o.orderNumber
    LEFT JOIN customers c ON o.customerNumber = c.customerNumber
    WHERE c.country IN ('Italy', 'Spain', 'France', 'UK') OR c.country IS NULL
    GROUP BY p.productCode, c.country
)
SELECT 
    p.productCode,
    p.productLine,
    p.productName,
    SUM(cps.sales_quantity) AS "Total Sales",
    MAX(CASE WHEN cps.country = 'Italy' THEN cps.sales_quantity END) AS "Italian Sales",
    MAX(CASE WHEN cps.country = 'Spain' THEN cps.sales_quantity END) AS "Spanish Sales",
    MAX(CASE WHEN cps.country = 'France' THEN cps.sales_quantity END) AS "French Sales",
    MAX(CASE WHEN cps.country = 'UK' THEN cps.sales_quantity END) AS "UK Sales"
FROM products p
LEFT JOIN Country_Product_Sales cps ON p.productCode = cps.productCode
GROUP BY p.productCode, p.productLine, p.productName
ORDER BY "Total Sales" DESC;

方案二：直接在聚合层用条件拆分（更简洁） #

无需预聚合CTE，直接在主查询中通过CASE WHEN条件聚合拆分各国家销量，一次关联完成所有计算：

SELECT 
    p.productCode,
    p.productLine,
    p.productName,
    SUM(od.quantityOrdered) AS "Total Sales",
    SUM(CASE WHEN c.country = 'Italy' THEN od.quantityOrdered END) AS "Italian Sales",
    SUM(CASE WHEN c.country = 'Spain' THEN od.quantityOrdered END) AS "Spanish Sales",
    SUM(CASE WHEN c.country = 'France' THEN od.quantityOrdered END) AS "French Sales",
    SUM(CASE WHEN c.country = 'UK' THEN od.quantityOrdered END) AS "UK Sales"
FROM products p
LEFT JOIN orderdetails od ON p.productCode = od.productCode
LEFT JOIN orders o ON od.orderNumber = o.orderNumber
LEFT JOIN customers c ON o.customerNumber = c.customerNumber
AND c.country IN ('Italy', 'Spain', 'France', 'UK')
GROUP BY p.productCode, p.productLine, p.productName
ORDER BY "Total Sales" DESC;

优化说明 #

1. 性能提升：仅执行一次表关联操作，避免原代码中5次重复的表连接与过滤计算，大幅减少数据库IO与CPU消耗
2. 逻辑清晰：用CASE WHEN条件聚合直接拆分各国家销量，代码结构更简洁，维护成本更低
3. 符合需求：通过LEFT JOIN确保所有产品都被返回，无销量时SUM(NULL)自动返回NULL，满足显示要求
4. 冗余消除：去掉原代码中不必要的GROUP BY（主查询已按产品唯一键聚合）

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