列出各个部门中工资高于本部门的平均工资的员工数和部门号，用exists

SELECT
DEPTCODE,
COUNT(*) AS 高于本部门的平均工资的员工数
FROM
T_EMPL main
WHERE
EXISTS(
SELECT
AVG( sub.SALARY ) as avg_SALARY
FROM
T_EMPL sub
WHERE
main.DEPTCODE = sub.DEPTCODE
HAVING
main.SALARY > AVG( sub.SALARY )
)
GROUP BY
DEPTCODE

执行结果:

DEPTCODE 高于本部门的平均工资的员工数
A1 1
B1 2

SELECT DEPTCODE,COUNT(CODE) 员工数
FROM T_EMPL
WHERE DEPTCODE,CODE
EXISTS(
SELECT DEPTCODE,CODE FROM T_EMPL A
LEFT JOIN
(SELECT DEPTCODE,AVG(SALARY) SALARY FROM T_EMPL GROUP BY DEPTCODE) B
ON A.DEPTCODE=B.DEPTCODE
WHERE A.SALARY>B.SALARY
)T