查询总销售额高于平均水平的销售员信息SQL问题求助

需求 #

显示总销售额高于所有销售员平均销售额的销售员的SID、SName和Location，销售额公式为 (Price - Price*Discount/100)*Quantity，需使用独立子查询。

表结构 #

Salesman表 #

Product表 #

Sale表 #

Saledetail表 #

现有SQL代码 #

SELECT S.Sid, S.Sname, S.Location
FROM Salesman S
WHERE (
SELECT SUM((P.Price - (P.Price * P.Discount / 100)) * SD.Quantity)
FROM SaleDetail SD
JOIN Sale SA ON SD.Saleid = SA.Saleid
JOIN Product P ON SD.Prodid = P.Prodid
WHERE SA.Sid = S.Sid
) > (
SELECT AVG(TotalSales)
FROM (
SELECT SUM((P.Price - (P.Price * P.Discount / 100)) * SD.Quantity) AS TotalSales
FROM SaleDetail SD
JOIN Sale SA ON SD.Saleid = SA.Saleid
JOIN Product P ON SD.Prodid = P.Prodid
GROUP BY SA.Sid
) AvgSales
);

当前输出 #

期望输出 #

问题原因与修正方案 #

问题根源 #

原查询计算平均销售额时，仅统计了有销售记录的销售员（SID1、2、4、5），忽略了无销售记录的销售员（SID3、6），导致平均销售额被拉高。Kevin的总销售额（99.5）低于这个拉高后的平均值（122.125），因此未被筛选出来。

正确的平均销售额应包含所有6名销售员，无销售记录的销售员总销售额按0计算，此时平均销售额降至约81.42，Kevin的销售额即可超过该值。

修正后的SQL代码 #

SELECT S.Sid, S.Sname, S.Location
FROM Salesman S
WHERE (
    -- 计算当前销售员的总销售额，无销售则返回0
    SELECT COALESCE(SUM((P.Price - (P.Price * P.Discount / 100)) * SD.Quantity), 0)
    FROM Sale SA
    LEFT JOIN SaleDetail SD ON SA.Saleid = SD.Saleid
    LEFT JOIN Product P ON SD.Prodid = P.Prodid
    WHERE SA.Sid = S.Sid
) > (
    -- 计算所有销售员的平均销售额（含无销售的）
    SELECT AVG(TotalSales)
    FROM (
        SELECT COALESCE(SUM((P.Price - (P.Price * P.Discount / 100)) * SD.Quantity), 0) AS TotalSales
        FROM Salesman S
        LEFT JOIN Sale SA ON S.Sid = SA.Sid
        LEFT JOIN SaleDetail SD ON SA.Saleid = SD.Saleid
        LEFT JOIN Product P ON SD.Prodid = P.Prodid
        GROUP BY S.Sid
    ) AvgSales
);

验证计算 #

各销售员总销售额：

• SID1: 303
• SID5: 99.5
• SID2: 60.5
• SID4: 25.5
• SID3: 0
• SID6: 0

所有销售员平均销售额：(303+99.5+60.5+25.5+0+0)/6 ≈81.42

Kevin的99.5 > 81.42，因此会被纳入结果集，符合预期输出。

内容的提问来源于stack exchange，提问作者gayatri pant Baby name Pragya