Northwind数据库SQL查询问题：员工利润统计异常排查

你遇到的TotalOrders异常是因为多表关联后订单记录被重复展开：一个订单对应多个订单项（OrderDetails里的多条记录），JOIN之后同一个订单会被拆分成和订单项数量一致的行，直接用COUNT(O.OrderID)会把同一个订单重复计数，导致数值虚高。

方案1：用COUNT(DISTINCT)统计唯一订单数 #

直接修改计数逻辑，用DISTINCT限定只统计唯一的订单ID，就能得到真实的订单数量，同时保留利润计算逻辑：

SELECT 
    E.EmployeeID, 
    E.FirstName, 
    E.LastName, 
    COUNT(DISTINCT O.OrderID) AS TotalOrders, -- 去重统计唯一订单
    SUM(P.Price * OD.Quantity) AS TotalProfit
FROM Employees E
JOIN Orders O ON E.EmployeeID = O.EmployeeID
JOIN OrderDetails OD ON O.OrderID = OD.OrderID
JOIN Products P ON OD.ProductID = P.ProductID
GROUP BY E.EmployeeID, E.FirstName, E.LastName
ORDER BY TotalProfit DESC;

方案2：先聚合订单级利润再关联 #

先在子查询里算出每个订单的总营收，再和员工、订单表关联统计，从根源避免重复行问题，数据量大时性能更优：

SELECT 
    E.EmployeeID, 
    E.FirstName, 
    E.LastName, 
    COUNT(O.OrderID) AS TotalOrders,
    SUM(OrderTotal.Total) AS TotalProfit
FROM Employees E
JOIN Orders O ON E.EmployeeID = O.EmployeeID
JOIN (
    -- 先计算单个订单的总营收
    SELECT 
        OD.OrderID, 
        SUM(P.Price * OD.Quantity) AS Total
    FROM OrderDetails OD
    JOIN Products P ON OD.ProductID = P.ProductID
    GROUP BY OD.OrderID
) OrderTotal ON O.OrderID = OrderTotal.OrderID
GROUP BY E.EmployeeID, E.FirstName, E.LastName
ORDER BY TotalProfit DESC;

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