mysql查询出平均工资最高的部门编号
一.部门表
DROP TABLE IF EXISTS `depts`;
CREATE TABLE `depts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`description` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;
-- ----------------------------
-- Records of depts
-- ----------------------------
INSERT INTO `depts` VALUES ('1', '人事部', null);
INSERT INTO `depts` VALUES ('2', '研发部', null);
INSERT INTO `depts` VALUES ('3', '市场部', null);
INSERT INTO `depts` VALUES ('4', '培训部', null);二.员工表
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`employee_id` int(11) NOT NULL AUTO_INCREMENT,
`dept_id` int(10) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`gender` varchar(10) DEFAULT NULL,
`join_time` datetime DEFAULT NULL,
`salary` float(10,2) DEFAULT NULL,
`address` varchar(10) DEFAULT NULL,
`image` varchar(10) DEFAULT NULL,
`description` varchar(10) DEFAULT NULL,
`job` varchar(10) NOT NULL,
PRIMARY KEY (`employee_id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=gbk;
-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES ('1', '1', '张三', 'M', '-02-02 12:12:12', '3500.00', '北京', null, null, 'a');
INSERT INTO `employees` VALUES ('2', '2', '李四', 'F', '-02-20 13:13:13', '5000.00', '福建', null, null, 'b');
INSERT INTO `employees` VALUES ('3', '3', '王五', 'M', '-12-30 14:14:14', '7000.00', '福建', null, null, 'a');
INSERT INTO `employees` VALUES ('4', '1', '赵六', 'F', '-06-06 15:15:15', '2800.00', '河北', null, null, 'b');
INSERT INTO `employees` VALUES ('5', '2', '钱七', 'M', '-08-21 09:09:09', '8000.00', '河北', null, null, 'a');
INSERT INTO `employees` VALUES ('6', '3', '孙八', 'F', '-04-16 10:10:20', '2000.00', '北京', null, null, 'b');三.sql语句
select a.id,a.name,b.avg_sa from depts a left join (SELECT d.id, d.name, AVG(e.salary)
as avg_sa FROM employees e, depts d WHERE e.dept_id = d. id group by e.dept_id) b on
a.id=b.id where b.avg_sa>=(select max(f.avg_sa) from (SELECT avg(salary) as avg_sa FROM
employees group by dept_id) f) ;四.运行结果：
