关联子查询

需求：查询不同职位大于平均工资的员工

表结构：

CREATE TABLE `t_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名字',
  `job` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '职位',
  `salary` decimal(10,2) DEFAULT NULL COMMENT '工资',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='员工表';

常规思路：

第一步：对职位进行分组，查询平均工资。

select avg(salary) from t_employee group by job

第二步：查询工资大于同职位平均工资的员工。（问题：子查询返回多条记录）

select * from t_employee where salary > (select avg(salary) from t_employee group by job)

正确思路：关联子查询

第一步：传入员工的职位，查询职位的平均工资

select avg(salary) from t_employee where job = 'manager'

第二步：根据平均工资，查询大于平均工资的员工

select * from t_employee e where salary> (select avg(salary) from t_employee where job = 'manager')

第三步：子查询职位的平均工资关联到主查询中

select * from t_employee e where salary> (select avg(salary) from t_employee where e.job = job)