SQL技术求助：查询每位销售业绩及更高业绩销售的平均业绩

我来帮你搞定这个需求！要实现“列出每位salesman的销售额，以及销售额高于该salesman的其他salesman的平均销售额”，我们可以通过预计算销售额+自连接聚合的方式来实现，逻辑清晰还能避免重复计算。

完整SQL代码（推荐用CTE，简洁高效） #

WITH salesman_sales AS (
    -- 第一步：先算出每个salesman的总销售额，封装成临时结果集
    SELECT 
        s.salesman,
        SUM(p.price * d.qty) AS total_sales
    FROM salesman s
    JOIN invoices i ON i.salesman = s.salesman
    JOIN detail d ON d.ino = i.ino
    JOIN parts p ON p.pno = d.pno
    GROUP BY s.salesman
)
-- 第二步：自连接计算高于当前销售的平均销售额
SELECT 
    ss1.salesman,
    ss1.total_sales,
    -- 用AVG计算符合条件的销售额平均值，LEFT JOIN确保无更高销售额时返回NULL
    AVG(ss2.total_sales) AS avg_higher_sales
FROM salesman_sales ss1
LEFT JOIN salesman_sales ss2 
    ON ss2.total_sales > ss1.total_sales  -- 关联条件：其他销售的销售额更高
GROUP BY ss1.salesman, ss1.total_sales
ORDER BY ss1.salesman;

代码逻辑拆解 #

1. CTE salesman_sales：这一步把你原来的多表关联聚合逻辑封装成一个临时结果集，一次性算出所有salesman的总销售额。这么做的好处是避免重复写复杂的关联查询，代码更易读，性能也更好（只计算一次销售额）。
2. 自连接关联：把salesman_sales自连接，ss1代表当前要统计的salesman，ss2代表所有销售额比ss1高的salesman。用LEFT JOIN而不是INNER JOIN，是为了保证即使某个salesman是销售额最高的（没有比他高的同行），也能正常返回结果，此时avg_higher_sales会是NULL。
3. 聚合计算平均值：对每个ss1的salesman，聚合所有符合ss2.total_sales > ss1.total_sales的ss2.total_sales，取平均值就是我们需要的“高于该salesman的其他salesman的平均销售额”。

兼容老版本数据库的写法（无CTE时用子查询） #

如果你的数据库不支持CTE（比如MySQL 5.7及以前版本），可以用子查询代替CTE，逻辑是一样的：

SELECT 
    ss1.salesman,
    ss1.total_sales,
    AVG(ss2.total_sales) AS avg_higher_sales
FROM (
    SELECT 
        s.salesman,
        SUM(p.price * d.qty) AS total_sales
    FROM salesman s
    JOIN invoices i ON i.salesman = s.salesman
    JOIN detail d ON d.ino = i.ino
    JOIN parts p ON p.pno = d.pno
    GROUP BY s.salesman
) ss1
LEFT JOIN (
    SELECT 
        s.salesman,
        SUM(p.price * d.qty) AS total_sales
    FROM salesman s
    JOIN invoices i ON i.salesman = s.salesman
    JOIN detail d ON d.ino = i.ino
    JOIN parts p ON p.pno = d.pno
    GROUP BY s.salesman
) ss2 ON ss2.total_sales > ss1.total_sales
GROUP BY ss1.salesman, ss1.total_sales
ORDER BY ss1.salesman;

可选优化：处理NULL值 #

如果希望当没有更高销售额的salesman时，avg_higher_sales显示0而不是NULL，可以用COALESCE函数替换AVG部分：

COALESCE(AVG(ss2.total_sales), 0) AS avg_higher_sales

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