mysql连接查询练习题(一)
一.题目所需的三张表
dept(部门表)
emp(员工表)
salgrade(薪水级别表)
二.习题
1.取得每个部门最高薪水的人员名称
- 取得每个部门的最高薪水
select deptno,max(sal) as maxsal from emp group by deptno;//每部门最高薪水
- 连接这张表进行查询
select
e.ename ,t.*
from
(select deptno,max(sal) as maxsal from emp group by deptno)t
join
emp e
on
t.deptno=e.deptno and t.maxsal=e.sal;
2.哪些人的薪水在部门的平均薪水之上
- 取得每个部门的平均薪水
- 进行连接查询
select
e.ename,e.sal,t.*
from
emp e
join
(select deptno,avg(sal) as avgsal from emp group by deptno)t
on
t.deptno=e.deptno and e.sal>t.avgsal;
注意on后面的条件一定要有t.deptno=e.deptno,否则:
每个员工的薪水会和所有部门的平均薪水比较,满足就显示。正解如下:
3.取得部门中平均薪水的等级
- 取得每个部门的平均薪水
- 进行连接查询
select
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno)t
join
salgrade s
on
t.avgsal between s.local and s.hisal;
4.取得平均薪水最高的部门的部门编号(平均薪水可能相同)
- 取得每个部门的平均薪水
- 对分组的部门薪水通过having再次进行筛选
select
deptno,avg(sal) as avgsal
from
emp group by deptno
having
avgsal=(select max(t.avgsal) from
(select avg(sal) avgsal from emp group by deptno) t)
5.取得平均薪水最高的部门的部门名称
- 取得每个部门的名称与平均部门薪水
select
d.dname,avg(e.sal) avgsal
from
emp e
join
dept d
on
e.deptno=d.deptno
group by
d.dname;
- 对结果用having进行过滤(条件是等于最高的部门薪水)
select
d.dname,avg(e.sal) avgsal
from
emp e
join
dept d
on
e.deptno=d.deptno
group by
d.dname
having
avgsal=(select max(t.avgsal) from
(select avg(sal) avgsal from emp group by deptno) t)
6.求平均薪水的等级最高的部门的部门名称(平均薪水不是最高,但等级最高)
- 取得每个部门平均薪水的等级
select
t.*,s.grade
from
(select deptno,avg(sal) avgsal from emp group by deptno)t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
- 取得每个部门平均薪水的等级和部门名称
select
t.*,s.grade
from
(select d.dname,avg(e.sal) avgsal from emp e join dept d on d.deptno=e.deptno group by d.dname)t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
- 对结果使用where条件对结果进行筛选(having是用在group by 之后)
select
t.*,s.grade
from
(select d.dname,avg(e.sal) avgsal from emp e join dept d on d.deptno=e.deptno group by d.dname)t
join
salgrade s
on
t.avgsal between s.losal and s.hisal
where
s.grade=(select max(s.grade) from (select avg(sal) avgsal from emp group by deptno)t join salgrade s on t.avgsal between s.losal and s.hisal);
未完待续....