--查出每个部门的最高工资,并在每个最高工资中找出最低的那个
SELECT min(max(salary)) from employees group by department_id;
group by department_id;组函数中能套组函数,但不能写成这样
select min(select max(salary) from employees group by department_id) from employees,应该 是组函数不支持这种写法。
--查询平均工资最低的部门信息和该部门的平均工资
--可以在要查询里,添加查询语句
select d.*,(select avg(salary) from employees where d.department_id=department_id) from departments d
where department_id=(
select department_id from employees having avg(salary)=(select min(avg(salary)) from employees
group by department_id)
group by department_id
);
支持这样的写法,会在找到的每行last_name中添加 字符串
select '字符串', last_name from employees ;
--查询各部门中工资比本部门平均工资高的员工的员工号,姓名,工资
以下能查出
select employee_id,last_name,salary, department_id from employees e1 where salary >(select avg(salary) from employees e2 where e2.department_id= e1.department_id )
and e1.department_id in (select department_id from employees);
也能查出
select employee_id,last_name,salary, department_id from employees e1
where salary >(select avg(salary) from employees e2 where e1.department_id= e2.department_id group by department_id );