统计每位客户订单总数与商品总数的SQL查询问题

数据表结构 #

• customers（CustomerID）
• orders（OrderID, CustomerID）
• orders_products（OrderID, ProductID）

需求 #

查询每位客户对应的订单总数和订购商品总数，期望结果格式如下：

问题更新（含示例数据） #

customers表 #

orders表 #

orders_products表 #

错误的查询语句及结果 #

使用以下查询语句：

SELECT c.CustomerID, COUNT(o.OrderID) AS TotalOrders, COUNT(op.ProductID) AS TotalProducts 
FROM customers AS c INNER JOIN orders AS o 
ON c.CustomerID = o.CustomerID 
INNER JOIN orders_products AS op 
ON o.OrderID = op.OrderID 
GROUP BY c.CustomerID 
ORDER BY TotalOrders DESC, TotalProducts DESC 

得到错误结果：

问题说明：CustomerID为1的客户实际有3个订单、4件商品，但查询结果中订单数被错误统计为4，原因是关联三张表后，一个包含多件商品的订单会被拆分成多条记录，导致COUNT(o.OrderID)重复计数。

正确的解决方案 #

方法一：使用COUNT(DISTINCT)去重统计订单数 #

通过对OrderID去重，避免重复计数：

SELECT 
    c.CustomerID,
    COUNT(DISTINCT o.OrderID) AS TotalOrders,
    COUNT(op.ProductID) AS TotalProducts
FROM customers AS c
INNER JOIN orders AS o ON c.CustomerID = o.CustomerID
INNER JOIN orders_products AS op ON o.OrderID = op.OrderID
GROUP BY c.CustomerID
ORDER BY TotalOrders DESC, TotalProducts DESC;

方法二：先聚合子表再关联（更高效） #

先分别统计每个客户的订单数、商品数，再关联到客户表，避免数据膨胀：

SELECT 
    c.CustomerID,
    o.TotalOrders,
    op.TotalProducts
FROM customers AS c
INNER JOIN (
    -- 统计每个客户的订单总数
    SELECT CustomerID, COUNT(OrderID) AS TotalOrders
    FROM orders
    GROUP BY CustomerID
) AS o ON c.CustomerID = o.CustomerID
INNER JOIN (
    -- 统计每个客户的商品总数
    SELECT o.CustomerID, COUNT(op.ProductID) AS TotalProducts
    FROM orders AS o
    INNER JOIN orders_products AS op ON o.OrderID = op.OrderID
    GROUP BY o.CustomerID
) AS op ON c.CustomerID = op.CustomerID
ORDER BY o.TotalOrders DESC, op.TotalProducts DESC;

两种方法均能得到期望的正确结果：

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