赞
踩
select 语句当中嵌套select语句,被嵌套的select语句时子查询。
子查询可以出现在哪里?
select
...(select)...
from
...(select)...
where
...(select)...
案例:找出高于平均薪资的员工信息。
select * from emp where sal > avg(sal); //错误
以上是一种错误的写法,牢记:where后面不能直接使用分组函数
正确写法:
第一步:找出平均薪资;
select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
第二步:where过滤
select * from emp where sal > 2073.214286 ;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.00 sec)
第一步和二合并步(在where子句中使用了子查询):
select * from emp where sal > (select avg(sal) from emp);
查询结果同上。
案例1: 找出每个部门平均薪水的薪资等级
第一步:计算每个部门的平均工资(按照部门编号分组):
select deptno, avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
第二步:对每个部门的平均工资进行分级:
我们可以将上面查询出的结果当作一张临时新表t,与表salgrade进行连接查询,条件为
t.avgsal between s.losal and s.hisal
select
t.deptno, t.avgsal, s.grade
from
(select deptno, avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
+--------+-------------+-------+
3 rows in set (0.00 sec)
案例2: 找出每个部门平均的薪资等级。
第一步:求出每个部门每个员工的薪水等级
select
e.deptno, e.ename, e.sal, s.grade
from
emp e
join
salgrade s
on
e.sal between losal and hisal;
+--------+--------+---------+-------+ | deptno | ename | sal | grade | +--------+--------+---------+-------+ | 20 | SMITH | 800.00 | 1 | | 30 | ALLEN | 1600.00 | 3 | | 30 | WARD | 1250.00 | 2 | | 20 | JONES | 2975.00 | 4 | | 30 | MARTIN | 1250.00 | 2 | | 30 | BLAKE | 2850.00 | 4 | | 10 | CLARK | 2450.00 | 4 | | 20 | SCOTT | 3000.00 | 4 | | 10 | KING | 5000.00 | 5 | | 30 | TURNER | 1500.00 | 3 | | 20 | ADAMS | 1100.00 | 1 | | 30 | JAMES | 950.00 | 1 | | 20 | FORD | 3000.00 | 4 | | 10 | MILLER | 1300.00 | 2 | +--------+--------+---------+-------+ 14 rows in set (0.00 sec)
第二步:对上述结果看做一张新表t,对每个部门薪资等级的平均值(对部门进行分组求均值);
select
t.deptno, avg(t.grade)
from
(select e.deptno, e.ename, s.grade from emp e join salgrade s on e.sal between losal and hisal) t
group by
t.deptno;
+--------+--------------+
| deptno | avg(t.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
3 rows in set (0.00 sec)
但是,这里实际上没必要把新表看做一张临时表,这里其实可以直接写:
select
e.deptno, avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between losal and hisal
group by
deptno;
查询结果与上述一致:
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
案例: 找出每个员工所在的部门名称,要求显示员工名和部门名;
方法一:使用内连接的方式:
(1)第一步: 找出每个员工所在的部门编号:
select ename, deptno from emp;
(2)第二步: 关联dept表:
select e.ename, e.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
查询结果:
+--------+--------+------------+ | ename | deptno | dname | +--------+--------+------------+ | SMITH | 20 | RESEARCH | | ALLEN | 30 | SALES | | WARD | 30 | SALES | | JONES | 20 | RESEARCH | | MARTIN | 30 | SALES | | BLAKE | 30 | SALES | | CLARK | 10 | ACCOUNTING | | SCOTT | 20 | RESEARCH | | KING | 10 | ACCOUNTING | | TURNER | 30 | SALES | | ADAMS | 20 | RESEARCH | | JAMES | 30 | SALES | | FORD | 20 | RESEARCH | | MILLER | 10 | ACCOUNTING | +--------+--------+------------+
方法二:在select后面嵌入子查询
第一步:找出部门名:
select deptno, dname from dept;
第二步:嵌套查询
select
e.ename, e.deptno, (select dname from dept d where d.deptno = e.deptno) as dname
from
emp e;
查询结果与方法一结果一致。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。