4.4子查询

-- 查询各部门员工人数占比

-- 查询整体员工数量

select count(*) from emp;

将整体员工数量作为子查询来查询各部门员工人数占比

select deptno,count(x) 员工人数

count(*)/(select count(x) from emp) 员工人数占比

from emp group by deptno;

-- 查询各部门最高工资

select deptno,max(sal) from emp group by deptno;

将各部门最高工资作为子查询，找出所有部门内部员工工资等于最高工资的员工

select empno,ename,sal,emp.deptno

from emp

left join (select deptno,max(sal) as最高工资from emp group by deptno) as t

on emp.deptno=t.deptno

where sal= 最高工资 ;

--where 本身只是查找所有工资等于最大值的记录，返回记录顺序是随机的如果需要有序返回，可以使用 order by 子句

-- 也可以直接使用开窗函数对工资进行排名，查询排名为 1的员工

select empno,ename,sal,deptno

from(select *,dense rank() over(partition by deptno order by sal desc) 工资排名 from emp) t

where 工资排名 =1;

-- 查询公司所有员工的平均工资

select avg(sal) from emp;

-- 使用比较操作字符将员工工资与子查询返回的平均工资进行比较

select * from emp where sal>(select avg(sal) from emp);

-- 查询 smith 的部门和职位

select deptno,job from emp where ename='smith';

使用比较操作字符进行子查询-

select empno,ename,job,deptno

from emp

where (deptno,job)=(select deptno,job from emp where ename='smith') and ename<>'smith';

查询各部门最高工资的员工

select empno,ename,sal,deptno

from emp

where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

查询哪些人不是普通员工，即领导的工号

select distinct mgr from emp where mgr is not null;

上述代码的查询结果如图3-103所示

将第一步作为子查询来筛选数据，具体整合后的代码如下

-- 将领导的工号作为子查询来筛选数据

select empno,ename,sal

from emp

where empno not in (select distinct mgr from emp where mgr is not null);

-- 查询公司部门情况，若公司有 20 的部门，则返回该部门的员工情况

select *

from emp

where exists (select *from dept where deptno=20) and deptno =20;

--exists 后接的子查询并不对数据进行筛选，只决定外查询执不执行-

select *

from emp

where exists (select * from dept where deptno=20);

- 查询 30 号部门所有员工的基本工资

select sal from emp where deptno=30;

-- 将 30 号部门所有员工的基本工资作为子查询对记录进行筛选

Select *

from emp

where sal>any(select sal from emp where deptno=30) and deptno<>30;

--查询基本工资高于 30 号部门所有员工的员工信息

--将any 改为 all

select *

from emp

where sal>all(select sal from emp where deptno=30);