在人事系统或ERP报表中，经常需要回答一个具体问题：某个部门内部，哪些员工的工资比该部门的平均工资更高。这个问题看似简单，但如果用错写法，不仅SQL冗长，还可能因为关联逻辑出错导致数据不对。下面用一张员工表来演示两种主流实现方式。
一、使用子查询关联计算部门平均
最容易被想到的办法，是先通过GROUP BY算出每个部门的平均工资，再和原表做连接，最后用WHERE过滤。这种写法在几乎所有关系型数据库中都支持，兼容性最好。
假设有表emp，字段为id、name、dept_id、salary。我们首先写出平均工资的派生表：
SELECT dept_id, AVG(salary) AS avg_sal FROM emp GROUP BY dept_id
然后将原表与这个派生表按dept_id关联，并筛选出工资更高的记录：
SELECT e.id, e.name, e.dept_id, e.salary, a.avg_sal
FROM emp e
JOIN (
SELECT dept_id, AVG(salary) AS avg_sal
FROM emp
GROUP BY dept_id
) a ON e.dept_id = a.dept_id
WHERE e.salary > a.avg_sal
这种写法的优点是非常直观，初学者容易理解。但它需要对emp表扫描至少两次：一次用于聚合，一次用于关联。如果表很大且没有合适索引，性能开销明显。另外，当dept_id存在NULL时，NULL不会和任何值匹配，这部分员工会被直接排除，需要额外用COALESCE处理。
二、使用AVG OVER窗口函数
窗口函数可以在不改变行数的情况下，为每一行计算出所在分区的聚合值。AVG OVER (PARTITION BY dept_id) 会为同一部门的每行都填上该部门的平均工资，无需子查询和JOIN。
我们用一个子查询或CTE把窗口函数结果算出来，再在外层过滤：
SELECT id, name, dept_id, salary, dept_avg
FROM (
SELECT
id,
name,
dept_id,
salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM emp
) t
WHERE salary > dept_avg
这段SQL只扫描一次emp表，由数据库优化器在窗口计算阶段完成分区均值。代码更短，逻辑也更贴近业务描述：先算部门平均，再比大小。在MySQL 8.0、PostgreSQL、SQL Server、Oracle等现代数据库中均能正确执行。
需要注意的是，如果salary列存在NULL，AVG会忽略NULL，但员工那一行本身若salary为NULL，则比较结果也是未知，不会被选出。若业务要求把NULL视作0参与比较，应改用COALESCE(salary,0)。
三、两种方案对比与选型
我们从可读性、性能、兼容性三个维度做一张对照表：
| 维度 | 子查询关联 | AVG OVER窗口 |
|---|---|---|
| 代码行数 | 较多 | 较少 |
| 表扫描次数 | 通常两次 | 通常一次 |
| 数据库支持 | 几乎所有 | 需支持窗口函数 |
| NULL处理 | 关联自动丢NULL部门 | AVG忽略NULL值 |
从对比可以看出，只要数据库版本支持窗口函数，AVG OVER方案在维护和性能上都有优势。但在老旧系统如MySQL 5.7中，只能使用子查询写法。
实际项目中，若部门字段上有索引，子查询的聚合也会较快；而窗口函数若配合PARTITION BY字段的索引，同样能减少排序成本。因此选型时先确认数据库版本，再结合团队熟悉度决定。
四、完整可运行示例
下面给出一个包含建表和数据插入的完整示例，方便你在测试库直接验证：
CREATE TABLE emp (
id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
salary DECIMAL(10,2)
);
INSERT INTO emp VALUES
(1, '张三', 10, 8000),
(2, '李四', 10, 12000),
(3, '王五', 10, 9500),
(4, '赵六', 20, 7000),
(5, '钱七', 20, 11000),
(6, '孙八', 20, 11000);
-- 窗口函数写法
SELECT id, name, dept_id, salary, dept_avg
FROM (
SELECT
id,
name,
dept_id,
salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM emp
) t
WHERE salary > dept_avg;
执行后，部门10中李四工资12000高于平均9833将被选出；部门20中钱七和孙八工资11000高于平均9666也会被选出。这样就能精确找出每个部门里高于平均水平的员工。
总结来说，计算部门内工资高于平均，AVG OVER对比子查询在表达力和效率上更优，但务必确认数据库支持窗口函数并处理好NULL情况。写报表时优先用窗口函数，能让SQL更贴近分析语义。