如何实现按国家统计已发货订单的总营收？SQL技术问询

需求说明 #

需要生成包含country和total_revenue两列的结果表，统计规则如下：

• 每条订单明细的营收计算公式：quantityordered * price_each
• 仅统计orders表中status为shipped的订单，单个订单的营收为其所有对应明细的营收之和
• 通过ordernumber关联orderdetails与orders表，通过customer_num关联orders与customers表，最终按国家汇总符合条件的所有订单营收

遇到的问题 #

尝试先按ordernumber或customer_num分组生成含营收与customer_num的中间表，再关联customers表二次分组，但结果异常。

相关表结构示例 #

orderdetails表 #

orders表 #

customers表 #

解决方案 #

正确SQL语句 #

直接关联三张表，先过滤已发货订单，计算每条明细的营收后按国家分组汇总，避免二次分组带来的重复计算问题：

SELECT 
    c.country,
    SUM(od.quantityordered * od.price_each) AS total_revenue
FROM 
    orderdetails od
INNER JOIN 
    orders o ON od.ordernumber = o.ordernumber
INNER JOIN 
    customers c ON o.customer_num = c.custom_num
WHERE 
    o.status = 'shipped'
GROUP BY 
    c.country;

异常原因分析 #

你之前的二次分组方法出错，大概率是因为关联过程中产生了重复数据：

• 若先按ordernumber计算单订单营收，再关联orders表时，若同一个订单对应多个customer_num（比如示例中订单1同时关联11和12），会导致该订单的营收被重复计入多个客户的统计中，最终二次分组时数据翻倍
• 若先按customer_num分组，未提前过滤未发货订单或未正确聚合订单明细，也会导致营收计算错误

示例数据计算结果 #

用你提供的示例数据运行上述SQL，得到的正确结果应为：

（注：你提供的预期结果表存在列值混淆问题，把customer_num当成了country列，实际应显示国家名称）

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