Oracle经典子查询进阶

最新推荐文章于 2024-07-11 03:05:56 发布

gnuapvb

最新推荐文章于 2024-07-11 03:05:56 发布

阅读量422

点赞数

分类专栏： SQL 文章标签：数据库开发 oracle sql

本文链接：https://blog.csdn.net/singingmyblues/article/details/124026375

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

查询比本部门平均工资高的员工的last_name, department_id, salary及平均工资

select last_name, department_id, salary, (select avg(salary) from employees where department_id = e1.department_id)
from employees e1
where salary > (
      select avg(salary)
      from employees e2
      where e1.department_id = e2.department_id
)
 
select last_name, e1.department_id, salary, avg_sal
from employees e1, (
     select department_id, avg(salary) avg_sal
     from employees
     group by department_id
) e2
where e1.department_id = e2.department_id
and e1.salary > e2.avg_sal;