mysql最高平均工资_mysql查询出平均工资最高的部门编号

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', '2008-02-02 12:12:12', '3500.00', '北京', null, null, 'a');

INSERT INTO `employees` VALUES ('2', '2', '李四', 'F', '2007-02-20 13:13:13', '5000.00', '福建', null, null, 'b');

INSERT INTO `employees` VALUES ('3', '3', '王五', 'M', '2006-12-30 14:14:14', '7000.00', '福建', null, null, 'a');

INSERT INTO `employees` VALUES ('4', '1', '赵六', 'F', '2008-06-06 15:15:15', '2800.00', '河北', null, null, 'b');

INSERT INTO `employees` VALUES ('5', '2', '钱七', 'M', '2005-08-21 09:09:09', '8000.00', '河北', null, null, 'a');

INSERT INTO `employees` VALUES ('6', '3', '孙八', 'F', '2008-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) ;四.运行结果：