oracle组函数

来源：互联网发布：手机淘宝怎么备注留言编辑：程序博客网时间：2024/05/17 06:04

--查出每个部门的最高工资，并在每个最高工资中找出最低的那个

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 );

0 0