概述
子查询是什么
select语句嵌套select语句，被嵌套的select语句是子查询
子查询出现在哪里
1
2
3
4
5
6
| select
..(select).
from
..(select).
where
..(select).
|
嵌套子查询
找出高于平均薪资员工 where嵌套子查询
1
2
3
| select * from emp where sal > (
select avg(sal) from emp
);
|
找出每个部门平均薪水的薪资等级 from嵌套子查询
找出每个部门平均薪水
1
2
3
4
5
6
7
8
9
| mysql> select deptno, avg(sal) avgsal from emp group by deptno;
+
| deptno | avgsal |
+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+
3 rows in set (0.00 sec)
|
最终sql
1
2
3
4
5
6
7
8
9
10
11
12
| mysql> select tmp.*, s.grade
-> from (select deptno, avg(sal) avgsal from emp group by deptno) tmp
-> join salgrade s
-> on tmp.avgsal between s.losal and s.hisal;
+
| deptno | avgsal | grade |
+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
+
3 rows in set (0.00 sec)
|
找出每个部门的薪资等级的平均值
1
2
3
4
5
6
7
8
9
10
11
12
13
| mysql> select e.deptno, avg(s.grade)
-> from emp e
-> join salgrade s
-> on e.sal between s.losal and s.hisal
-> group by e.deptno;
+
| deptno | avg(s.grade) |
+
| 20 | 2.8000 |
| 30 | 2.5000 |
| 10 | 3.6667 |
+
3 rows in set (0.00 sec)
|
找出每个员工所在的部门名称 select嵌套子查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| mysql> select e.ename, (
-> select d.dname from dept d where e.deptno = d.deptno
-> ) dname
-> from emp e;
+
| ename | dname |
+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+
14 rows in set (0.00 sec)
|
