列出各个部门中工资高于本部门平均工资的员工信息

这是一个师兄弟的面试题，分享一下。

表结构

表数据

列出各个部门中工资高于本部门平均工资的员工信息

SELECT E1.EID, E1.DEPTID, TB1.AVGSAL
FROM EMPLOYEE E1, (
        SELECT E.DEPTID, AVG(E.SALARY) AS AVGSAL
        FROM EMPLOYEE E
        GROUP BY E.DEPTID
    ) TB1
WHERE E1.DEPTID = TB1.DEPTID
    AND E1.SALARY > TB1.AVGSAL;

列出各个部门中工资高于本部门平均工资的人数

SELECT E1.DEPTID, COUNT(*) AS 部门人数
FROM EMPLOYEE E1, (
        SELECT E.DEPTID, AVG(E.SALARY) AS AVGSAL
        FROM EMPLOYEE E
        GROUP BY E.DEPTID
    ) TB1
WHERE E1.DEPTID = TB1.DEPTID
    AND E1.SALARY > TB1.AVGSAL
GROUP BY E1.DEPTID;

建表语句

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `EID` int(11) NOT NULL,
  `ENAME` varchar(12) DEFAULT NULL,
  `SALARY` double(12,0) DEFAULT '0',
  `DEPTID` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`EID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `EMPLOYEE` VALUES ('1001', '人员1', '5000', 'IT部');
INSERT INTO `EMPLOYEE` VALUES ('1002', '人员2', '4852', 'IT部');
INSERT INTO `EMPLOYEE` VALUES ('1003', '人员3', '3568', 'IT部');
INSERT INTO `EMPLOYEE` VALUES ('1004', '人员4', '6500', 'IT部');
INSERT INTO `EMPLOYEE` VALUES ('1005', '人员5', '4500', '生产部');
INSERT INTO `EMPLOYEE` VALUES ('1006', '人员6', '3800', '生产部');
INSERT INTO `EMPLOYEE` VALUES ('1007', '人员7', '3950', '生产部');
INSERT INTO `EMPLOYEE` VALUES ('1008', '人员8', '6800', '运营部');
INSERT INTO `EMPLOYEE` VALUES ('1009', '人员9', '6500', '运营部');
INSERT INTO `EMPLOYEE` VALUES ('1010', '人员10', '7000', '运营部');