+-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 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 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.01 sec)
+--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.01 sec)
+-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+ 5 rows in set (0.01 sec)
第一步:查出每个部门的最高薪水:
select deptno,max(sal) as maxsal from emp group by deptno;
结果:
+--------+---------+ | deptno | maxsal | +--------+---------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+---------+ 3 rows in set (0.00 sec)
第二步:将第一步的查询结果当作一张临时表t
将t和emp表连接,条件为:
e.deptno = d.deptno and e.sal = t.maxsal;
select e.ename,t.deptno,t.maxsal from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) as t on e.deptno = t.deptno and e.sal = t.maxsal;
结果:
+-------+--------+---------+ | ename | deptno | maxsal | +-------+--------+---------+ | BLAKE | 30 | 2850.00 | | SCOTT | 20 | 3000.00 | | KING | 10 | 5000.00 | | FORD | 20 | 3000.00 | +-------+--------+---------+ 4 rows in set (0.00 sec)
第一步:找出各部门的平均薪水
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
将表t与emp表连接,连接条件:
t.deptno = e.deptno and e.sal > t.avgsal
select e.ename,e.sal,t.deptno,t.avgsal from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) as t on t.deptno = e.deptno and e.sal > t.avgsal;
结果:
+-------+---------+--------+-------------+ | ename | sal | deptno | avgsal | +-------+---------+--------+-------------+ | ALLEN | 1600.00 | 30 | 1566.666667 | | JONES | 2975.00 | 20 | 2175.000000 | | BLAKE | 2850.00 | 30 | 1566.666667 | | SCOTT | 3000.00 | 20 | 2175.000000 | | KING | 5000.00 | 10 | 2916.666667 | | FORD | 3000.00 | 20 | 2175.000000 | +-------+---------+--------+-------------+ 6 rows in set (0.00 sec)
第一步:找出所有员工的薪水等级
select e.ename,e.deptno,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal order by e.deptno;
结果:
+--------+--------+---------+-------+ | ename | deptno | sal | grade | +--------+--------+---------+-------+ | KING | 10 | 5000.00 | 5 | | MILLER | 10 | 1300.00 | 2 | | CLARK | 10 | 2450.00 | 4 | | SMITH | 20 | 800.00 | 1 | | ADAMS | 20 | 1100.00 | 1 | | SCOTT | 20 | 3000.00 | 4 | | FORD | 20 | 3000.00 | 4 | | JONES | 20 | 2975.00 | 4 | | MARTIN | 30 | 1250.00 | 2 | | TURNER | 30 | 1500.00 | 3 | | BLAKE | 30 | 2850.00 | 4 | | ALLEN | 30 | 1600.00 | 3 | | JAMES | 30 | 950.00 | 1 | | WARD | 30 | 1250.00 | 2 | +--------+--------+---------+-------+ 14 rows in set (0.00 sec)
第二步:将第一步得到的结果接着按照部门分组,计算每个部门的薪资等级均值
select e.deptno,avg(s.grade) as avggrade from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;
结果:
+--------+----------+ | deptno | avggrade | +--------+----------+ | 10 | 3.6667 | | 20 | 2.8000 | | 30 | 2.5000 | +--------+----------+ 3 rows in set (0.00 sec)
第一步:取得每个部门的平均薪水
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,将表t与salgrade表连接
select t.*,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) as t join salgrade s on t.avgsal between s.losal and s.hisal;
结果:
+--------+-------------+-------+ | deptno | avgsal | grade | +--------+-------------+-------+ | 30 | 1566.666667 | 3 | | 10 | 2916.666667 | 4 | | 20 | 2175.000000 | 4 | +--------+-------------+-------+ 3 rows in set (0.00 sec)
将薪水降序排列,使用limit,只拿出第一个工资
select sal from emp order by sal desc limit 1;
结果:
+---------+ | sal | +---------+ | 5000.00 | +---------+ 1 row in set (0.00 sec)
第一步:将emp表分为a表和b表,找出a表中薪水比b表低的所有人,然后去重
select distinct a.sal from emp a join emp b on a.sal < b.sal;
结果:
+---------+ | sal | +---------+ | 800.00 | | 1250.00 | | 1500.00 | | 1100.00 | | 950.00 | | 1300.00 | | 1600.00 | | 2850.00 | | 2450.00 | | 2975.00 | | 3000.00 | +---------+ 11 rows in set (0.00 sec)
第二步:再找出不在上一步查询结果中的那个人的工资。
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)
第一步:先取得每个部门的平均薪资
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)
第二步:将第一步得到的每个部门的平均薪资降序排列,用limit拿到第一个部门编号
select deptno from emp group by deptno order by avg(sal) desc limit 1;
结果:
+--------+ | deptno | +--------+ | 10 | +--------+ 1 row in set (0.00 sec)
第一步:先取得每个部门的平均薪资
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)
第二步:找出以上结果中avgsal最大的值
select max(t.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) as t;
结果:
+---------------+ | max(t.avgsal) | +---------------+ | 2916.666667 | +---------------+ 1 row in set (0.00 sec)
第三步:再根据最大平均薪资值找到相应部门编号
select deptno from emp group by deptno having avg(sal) = (select max(t.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) as t);
结果:
+--------+ | deptno | +--------+ | 10 | +--------+ 1 row in set (0.00 sec)
select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno #只有分组的字段才能跟在select后面 group by d.dname order by avgsal desc limit 1;
结果:
+------------+-------------+ | dname | avgsal | +------------+-------------+ | ACCOUNTING | 2916.666667 | +------------+-------------+ 1 row in set (0.00 sec)
第一步:获取每个部门的平均薪水,按部门名称分组
select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname;
结果:
+------------+-------------+ | dname | avgsal | +------------+-------------+ | ACCOUNTING | 2916.666667 | | RESEARCH | 2175.000000 | | SALES | 1566.666667 | +------------+-------------+ 3 rows in set (0.02 sec)
第二步:将第一步得到的结果看作临时表t,查询每个部门的平均薪水对应的薪资等级
select t.*,s.grade from (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) as t join salgrade s on t.avgsal between s.losal and s.hisal;
结果:
+------------+-------------+-------+ | dname | avgsal | grade | +------------+-------------+-------+ | SALES | 1566.666667 | 3 | | ACCOUNTING | 2916.666667 | 4 | | RESEARCH | 2175.000000 | 4 | +------------+-------------+-------+ 3 rows in set (0.00 sec)
第三步:获取平均薪资最低的薪资等级
平均薪资最低的薪资等级一定是最低的
先获取最低平均薪资
select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
再获取相应的最低薪资等级
select s.grade from salgrade s join (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) as t on t.avgsal between s.losal and s.hisal;
第四步:拿第二步中查询到的各部门平均薪资等级与第三步中查询到的最低薪资等级相比对
select t.*,s.grade from (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) as t join salgrade s on t.avgsal between s.losal and s.hisal where s.grade = (select s.grade from salgrade s join (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) as t on t.avgsal between s.losal and s.hisal);
从而得到平均薪资等级最低的部门的部门名称:
+-------+-------------+-------+ | dname | avgsal | grade | +-------+-------------+-------+ | SALES | 1566.666667 | 3 | +-------+-------------+-------+ 1 row in set (0.01 sec)