连接Orders表后姓氏首字母计数异常，如何修正SQL查询？

问题根源 #

1. 直接LEFT JOIN的错误原因：一个员工对应多条订单记录，关联后员工的工资数据会被重复计算（每条订单对应一行员工数据），导致SUM(Salaris)、Count()等统计值被放大，完全偏离真实结果。
2. 子查询方案的问题：内层按WerknemerID + 首字母分组，最终输出是每个员工一行，自然同一首字母会出现多条记录，不符合按首字母汇总的需求。

正确方案1：用CTE预聚合数据（推荐，性能更优） #

先分别聚合员工工资统计、订单金额统计，再关联得到最终结果，彻底避免数据重复：

-- 预聚合每个员工的总订单金额
WITH OrderTotals AS (
    SELECT 
        WerknemerID,
        SUM(orderbedrag) AS TotalOrders
    FROM Orders
    GROUP BY WerknemerID
),
-- 预聚合按姓氏首字母分组的员工工资统计
EmployeeStats AS (
    SELECT 
        LEFT(Achternaam, 1) AS '1e_Initiaal_Achternaam',
        CAST(AVG(Salaris) AS INT) AS 'Gem. Salaris',
        ROUND(CAST(SUM(Salaris) AS INT), 0) AS 'Salaris',
        COUNT(*) AS 'Eerste letter achternaam' -- 统计该首字母下的员工总数
    FROM Werknemer
    GROUP BY LEFT(Achternaam, 1)
)
-- 关联两个聚合结果，按首字母汇总订单金额
SELECT 
    es.[1e_Initiaal_Achternaam],
    es.[Gem. Salaris],
    es.[Salaris],
    es.[Eerste letter achternaam],
    COALESCE(SUM(ot.TotalOrders), 0) AS 'Order Waarde', -- 用COALESCE处理无订单的情况
    CASE 
        WHEN es.Salaris <> 0 THEN COALESCE(SUM(ot.TotalOrders), 0) / es.Salaris 
        ELSE 0 
    END AS 'omzet per € salaris'
FROM EmployeeStats es
LEFT JOIN Werknemer w ON es.[1e_Initiaal_Achternaam] = LEFT(w.Achternaam, 1)
LEFT JOIN OrderTotals ot ON w.WerknemerID = ot.WerknemerID
GROUP BY es.[1e_Initiaal_Achternaam], es.[Gem. Salaris], es.[Salaris], es.[Eerste letter achternaam]
ORDER BY es.[1e_Initiaal_Achternaam];

正确方案2：SELECT子查询直接统计首字母订单总额 #

如果不想用CTE，也可以在主查询的SELECT中嵌入子查询，直接按首字母汇总订单金额：

SELECT 
    LEFT(Achternaam, 1) AS '1e_Initiaal_Achternaam',
    CAST(AVG(Salaris) AS INT) AS 'Gem. Salaris',
    ROUND(CAST(SUM(Salaris) AS INT), 0) AS 'Salaris',
    COUNT(*) AS 'Eerste letter achternaam',
    -- 统计该首字母下所有员工的总订单金额
    COALESCE((
        SELECT SUM(orderbedrag) 
        FROM Orders o
        JOIN Werknemer w2 ON o.WerknemerID = w2.WerknemerID
        WHERE LEFT(w2.Achternaam, 1) = LEFT(Werknemer.Achternaam, 1)
    ), 0) AS 'Order Waarde',
    -- 计算每欧元工资的营业额，处理除数为0的情况
    CASE 
        WHEN ROUND(CAST(SUM(Salaris) AS INT), 0) <> 0 
        THEN COALESCE((
            SELECT SUM(orderbedrag) 
            FROM Orders o
            JOIN Werknemer w2 ON o.WerknemerID = w2.WerknemerID
            WHERE LEFT(w2.Achternaam, 1) = LEFT(Werknemer.Achternaam, 1)
        ), 0) / ROUND(CAST(SUM(Salaris) AS INT), 0) 
        ELSE 0 
    END AS 'omzet per € salaris'
FROM Werknemer
GROUP BY LEFT(Achternaam, 1)
ORDER BY LEFT(Achternaam, 1);

关键注意点 #

• 避免数据重复：核心思路是先聚合订单或员工数据，再关联，绝对不能先关联一对多的表再聚合，否则会导致统计值失真。
• COUNT的正确用法：COUNT(LEFT(Achternaam,1))等价于COUNT(*)，因为只要员工存在，首字母必然存在，用COUNT(*)更直观表示首字母对应的员工数量。
• 空值处理：用COALESCE把无订单的NULL转为0，避免统计结果出现空值；用CASE处理除数为0的情况，防止查询报错。

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