老杜 mySql自学笔记34道例题

1.取得每个部门最高薪资的人员

第一步：取出每一个部门当中薪资最高的人（按部门分类，找出每一组当中的最大值）

mysql> select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     20 | 3000.00 |
|     30 | 2850.00 |
|     10 | 5000.00 |

第二步：将以上查询结果当成一个新的临时表t，表t和emp进行表链接

链接的条件是t.detpno = emp.deptno 和 t.maxsal = emp.sal

select
e.ename,t.*
from
emp e
join
(select deptno,max(sal) as maxsal from emp group by deptno) t
on
t.deptno = e.deptno and t.maxsal = e.sal;
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

2.哪些人的平均薪水在部门平均值之上

第一步：取得所有部门薪水的平均值

select e.deptno,avg(sal) as avgsal from emp e group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

第二部：将每个部门的平均值看成一个新表t，进行表链接

条件：部门编号相同，emp表当中的sal大于t表当中的avgsal

 select
 t.* ,e.ename,e.sal
 from emp e
 join
 (select e.deptno,avg(sal) as avgsal from emp e group by deptno) t
 on
 e.deptno = t.deptno and e.sal>t.avgsal;
+--------+-------------+-------+---------+
| deptno | avgsal      | ename | sal     |
+--------+-------------+-------+---------+
|     30 | 1566.666667 | ALLEN | 1600.00 |
|     20 | 2175.000000 | JONES | 2975.00 |
|     30 | 1566.666667 | BLAKE | 2850.00 |
|     20 | 2175.000000 | SCOTT | 3000.00 |
|     10 | 2916.666667 | KING  | 5000.00 |
|     20 | 2175.000000 | FORD  | 3000.00 |
+--------+-------------+-------+---------+
6 rows in set (0.00 sec)

3.取得部门当中所有人的平均薪水等级

第一步：找出每一个人的薪水等级

emp和salgrade表进行链接

链接条件：e.sal between s.losal and s.hisal

 select e.ename,e.sal,e.deptno,s.grade
 from
 emp e
 join
 salgrade s
 on
 e.sal between s.losal and s.hisal order by deptno;
+--------+---------+--------+-------+
| ename  | sal     | deptno | grade |
+--------+---------+--------+-------+
| CLARK  | 2450.00 |     10 |     4 |
| KING   | 5000.00 |     10 |     5 |
| MILLER | 1300.00 |     10 |     2 |

| SMITH  |  800.00 |     20 |     1 |
| JONES  | 2975.00 |     20 |     4 |
| SCOTT  | 3000.00 |     20 |     4 |
| ADAMS  | 1100.00 |     20 |     1 |
| FORD   | 3000.00 |     20 |     4 |

| ALLEN  | 1600.00 |     30 |     3 |
| WARD   | 1250.00 |     30 |     2 |
| MARTIN | 1250.00 |     30 |     2 |
| BLAKE  | 2850.00 |     30 |     4 |
| TURNER | 1500.00 |     30 |     3 |
| JAMES  |  950.00 |     30 |     1 |
+--------+---------+--------+-------+

第二部：基于以上的结果继续根据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
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)

4.不用组函数Max获取最大值

第一种方案：降序limit 1

select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)

第二种方案：select max(sal) from emp;

第三种方案：表的自连接  not  in为对比  一个表里的元素在不在另一个表当中

mysql>  select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal<b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)

5.取得平均薪资最高的部门的部门编号

第一步：取得所有部门的平均薪资

select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |

第二部：上表当中最高值

第一种方案：

mysql> select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

第二种方案：

mysql> select max(t.avgsal) as maxsal from (select avg(sal) as avgsal from emp group by deptno) t;
+-------------+
| maxsal      |
+-------------+
| 2916.666667 |
+-------------+
1 row in set (0.00 sec)

select
deptno,avg(sal) as avgsal
from
emp
group by
deptno
having
avgsal = (select max(t.avgsal) as maxsal from (select avg(sal) as avgsal from e