SQL怎样计算每个部门中谁的工资高于平均水平

AVG() OVER(PARTITION BY dept) 是解决“谁高于本部门均值”最简方案，它单次扫描为每行实时计算所在部门平均工资，避免子查询性能差和JOIN易错问题，且需注意NULL、数据类型及dept为空等陷阱。

sql怎样计算每个部门中谁的工资高于平均水平_avg over对比

用 `AVG() OVER()` 快速算出部门平均工资并比对

直接在查询中为每行计算所在部门的平均工资，是解决“谁高于本部门均值”的最简方案。关键不是先聚合再 JOIN，而是用窗口函数实时下推均值。

常见错误是写成 SELECT dept, name, salary > AVG(salary) FROM emp GROUP BY dept —— 这语法不合法，且无法关联到具体人。

AVG(salary) OVER (PARTITION BY dept) 会为每一行生成其所在部门的平均工资（重复值）
必须和原始行保持 1:1 对应，才能做 salary > AVG(...) OVER(...) 这种逐行比较
注意 AVG() 默认包含 NULL 排除，但若 salary 字段本身有 NULL，会影响均值结果，建议提前 WHERE salary IS NOT NULL

为什么不能用子查询或 JOIN 做部门均值？

子查询如 (SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept) 虽然能跑通，但性能差、可读弱，尤其数据量大时每个员工都要触发一次独立扫描。

JOIN 写法更易出错：比如忘记 GROUP BY 或漏掉 DISTINCT，导致笛卡尔积或重复计数。

窗口函数天然单次扫描完成，执行计划更优
OVER(PARTITION BY dept) 的语义比子查询更贴近“按部门划分上下文”这一业务意图
某些数据库（如 MySQL 8.0+、PostgreSQL、SQL Server）对窗口函数优化充分；老版本 MySQL（

完整可运行示例（含布尔标记和筛选）

SELECT
  dept,
  name,
  salary,
  ROUND(AVG(salary) OVER (PARTITION BY dept), 2) AS dept_avg,
  salary > AVG(salary) OVER (PARTITION BY dept) AS above_dept_avg
FROM emp
WHERE salary IS NOT NULL;

如果只要看“高于者”，加 HAVING 不生效（窗口函数不能在 HAVING 里用），得套一层：

SELECT dept, name, salary, dept_avg
FROM (
  SELECT
    dept,
    name,
    salary,
    AVG(salary) OVER (PARTITION BY dept) AS dept_avg
  FROM emp
  WHERE salary IS NOT NULL
) t
WHERE salary > dept_avg;

ROUND(..., 2) 是为了显示友好，不影响比较逻辑
别在 WHERE 中直接写 salary > AVG(salary) OVER(...) —— 窗口函数不能出现在 WHERE 子句
如果部门只有 1 人，AVG() 就等于他本人工资，> 判断恒为 false，符合数学定义

容易被忽略的 NULL 和类型陷阱

工资字段如果是字符串型（比如带 “¥” 或逗号），AVG() 会静默转成 0 或报错，取决于数据库。务必确认 salary 是数值类型（DECIMAL 或 FLOAT）。

PostgreSQL 对类型敏感，AVG(text_col) 直接报错
MySQL 可能隐式转换但截断小数，导致均值偏差
SQL Server 中 AVG() 对整数列返回整数，如 AVG(1000,2000) 得 1500，但 AVG(1000,1500) 得 1250（非 1250.0），比较时虽无碍，但显示易误导
安全做法：显式转换，如 AVG(CAST(salary AS DECIMAL(10,2))) OVER(...)