// 找出每个部门的平均薪资等级
mysql> select
-> e.deptno,e.avgsal,s.grade
-> from
-> (select deptno,avg(sal) as avgsal from emp group by deptno) e
-> join
-> salgrade s
-> on
-> e.avgsal between s.losal and hisal;
+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)
分析:
把 select deptno,avg(sal) as avgsal from emp group by deptno
查询出来的结果当做一个临时表
mysql> select
-> e.deptno,d.dname,e.avgsal,s.grade
-> from
-> (select deptno,avg(sal) as avgsal from emp group by deptno) e
-> join
-> salgrade s
-> on
-> e.avgsal between s.losal and s.hisal
-> join
-> dept d
-> on
-> d.deptno = e.deptno
-> order by
-> e.deptno asc;
+--------+------------+-------------+-------+
| deptno | dname | avgsal | grade |
+--------+------------+-------------+-------+
| 10 | ACCOUNTING | 2916.666667 | 4 |
| 20 | RESEARCH | 2175.000000 | 4 |
| 30 | SALES | 1566.666667 | 3 |
+--------+------------+-------------+-------+
3 rows in set (0.00 sec)
案例3(from后面)
// 找出每个部门薪资等级的平均值
select
ee.deptno,avg(ee.grade)
from
(select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) ee
group by
ee.deptno;
+--------+---------------+
| DEPTNO | avg(ee.grade) |
+--------+---------------+
| 20 | 2.8000 |
| 30 | 2.5000 |
| 10 | 3.6667 |
+--------+---------------+
3 rows in set (0.00 sec)
// 找出每个部门薪资等级的平均值
// 这种方式效率要快些
// 第一步,找出每个员共的薪资等级
select
e.empno,ename,e.deptno,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
order by
e.deptno;
+-------+--------+--------+-------+
| empno | ename | deptno | grade |
+-------+--------+--------+-------+
| 7782 | CLARK | 10 | 4 |
| 7839 | KING | 10 | 5 |
| 7934 | MILLER | 10 | 2 |
| 7369 | SMITH | 20 | 1 |
| ... |
| ... |
| 7698 | BLAKE | 30 | 4 |
| 7844 | TURNER | 30 | 3 |
| 7900 | JAMES | 30 | 1 |
+-------+--------+--------+-------+
14 rows in set (0.00 sec)
// 第二步,基于第一步结果,按照deptno分组,求grade平均值
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
order by
e.deptno desc;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 30 | 2.5000 |
| 20 | 2.8000 |
| 10 | 3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)
select
ee.deptno,avg(ee.grade)
from
(select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) ee
group by
ee.deptno
order by
ee.deptno;
+--------+---------------+
| DEPTNO | avg(ee.grade) |
+--------+---------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+---------------+
3 rows in set (0.00 sec)
select
ee.deptno,avg(ee.grade)
from
(select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) ee
group by
ee.deptno
join
salgrade s
on
ee.depno = s.depno
order by
ee.deptno;
应该还得再套一次才可以
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join
salgrade s
on
ee.depno = s.depno
order by
ee.deptno' at line 7
案例4(select后面)
// 找出每个员工所在的部门名称,要求显示员工名和部门名
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
// 找出每个员工所在的部门名称,要求显示员工名和部门名
select
e.ename,
(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| ... |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+