1、取得每个部门最高薪水的人员名称
步骤一：先查询每个部门的最高工资
mysql> 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)
步骤二：把上面查询结果当成一张表，查询符合上表情况的人
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 | +-------+--------+---------+ 4 rows in set (0.00 sec)
2、哪些人的薪水在部门的平均薪水之上
步骤一：查询每个部门的平均薪资
mysql> select deptno,avg(sal) as avg_sal from emp group by deptno; +--------+-------------+ | deptno | avg_sal | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec)
步骤二：获取大于平均薪资的人
mysql> select e.ename, e.sal -> from emp e -> join (select deptno,avg(sal) as avg_sal from emp group by deptno) t -> on e.deptno=t.deptno and e.sal>t.avg_sal;
+-------+---------+ | ename | sal | +-------+---------+ | ALLEN | 1600.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | +-------+---------+ 6 rows in set (0.00 sec)
3、取得部门中（所有人的）平均的薪水等级
步骤一：获取每个人的薪资等级
mysql> select e.deptno,e.ename, s.grade -> from emp e -> join salgrade s -> on e.sal between s.losal and s.hisal; +--------+--------+-------+ | deptno | ename | grade | +--------+--------+-------+ | 20 | SMITH | 1 | | 30 | ALLEN | 3 | | 30 | WARD | 2 | | 20 | JONES | 4 | | 30 | MARTIN | 2 | | 30 | BLAKE | 4 | | 10 | CLARK | 4 | | 20 | SCOTT | 4 | | 10 | KING | 5 | | 30 | TURNER | 3 | | 20 | ADAMS | 1 | | 30 | JAMES | 1 | | 20 | FORD | 4 | | 10 | MILLER | 2 | +--------+--------+-------+ 14 rows in set (0.01 sec)
步骤二：基于上述结果分组
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) | +--------+--------------+ | 10 | 3.6667 | | 20 | 2.8000 | | 30 | 2.5000 | +--------+--------------+ 3 rows in set (0.00 sec)
4、不准用组函数（Max），取得最高薪水
方法一：desc降序排序，limit显示第一条数据
mysql> select sal from emp order by sal desc limit 1; +---------+ | sal | +---------+ | 5000.00 | +---------+ 1 row in set (0.00 sec)
方法二：表的自连接
mysql> select distinct a.sal from emp a join emp b where 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) mysql> select sal from emp -> where sal not in (select distinct a.sal from emp a join emp b where a.sal<b.sal); +---------+ | sal | +---------+ | 5000.00 | +---------+ 1 row in set (0.00 sec)
5、取得平均薪水最高的部门的部门编号
方法一
步骤一：获取每个部门的平均薪资
mysql> select deptno,avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec)
步骤二：排序
select t.deptno from (select deptno,avg(sal) as avg_sal from emp group by deptno) t order by t.avg_sal desc limit 1;
+--------+ | deptno | +--------+ | 10 | +--------+ 1 row in set (0.00 sec)
方法二：max
select t.deptno,max(t.avg_sal) from (select deptno,avg(sal) as avg_sal from emp group by deptno) t;
6、取得平均薪水最高的部门的部门名称
步骤一：获取平均薪资最高的部门
mysql> select deptno,avg(sal) avg_sal from emp group by deptno order by avg_sal desc limit 1; +--------+-------------+ | deptno | avg_sal | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+ 1 row in set (0.00 sec)
步骤二：内连接两张表
mysql> select d.dname -> from dept d -> join (select deptno,avg(sal) avg_sal from emp group by deptno order by avg_sal desc limit 1) t -> on d.deptno=t.deptno; +------------+ | dname | +------------+ | ACCOUNTING | +------------+ 1 row in set (0.00 sec)
7、求平均薪水的等级最低的部门的部门名称
步骤一：先求部门的平均薪资
mysql> select deptno,avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 3 rows in set (0.00 sec)
步骤二：再求部门的平均薪资对应的等级
mysql> select e.deptno, s.grade -> from (select deptno,avg(sal) avg_sal from emp group by deptno) e -> join salgrade s -> on e.avg_sal between s.losal and s.hisal; +--------+-------+ | deptno | grade | +--------+-------+ | 30 | 3 | | 10 | 4 | | 20 | 4 | +--------+-------+ 3 rows in set (0.00 sec)
步骤三：内连接部门名称表
select e.deptno, s.grade, d.dname from (select deptno,avg(sal) avg_sal from emp group by deptno) e join salgrade s on e.avg_sal between s.losal and s.hisal join dept d on d.deptno = e.deptno;
+--------+-------+------------+ | deptno | grade | dname | +--------+-------+------------+ | 30 | 3 | SALES | | 10 | 4 | ACCOUNTING | | 20 | 4 | RESEARCH | +--------+-------+------------+ 3 rows in set (0.00 sec)
步骤四：排序
select d.dname from (select deptno,avg(sal) avg_sal from emp group by deptno) e join salgrade s on e.avg_sal between s.losal and s.hisal join dept d on d.deptno = e.deptno order by s.grade limit 1;
+-------+ | dname | +-------+ | SALES | +-------+ 1 row in set (0.00 sec)
8、取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的 领导人姓名
步骤一：先找到所有的领导员工
mysql> select distinct mgr from emp; +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | NULL | | 7788 | | 7782 | +------+ 7 rows in set (0.00 sec)
注意：必须排除null
mysql> select distinct mgr from emp where mgr is not null; +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | 7788 | | 7782 | +------+ 6 rows in set (0.00 sec)
步骤二：找出不同员工在最高薪资
mysql> select max(sal) -> from emp -> where empno not in (select distinct mgr from emp where mgr is not null); +----------+ | max(sal) | +----------+ | 1600.00 | +----------+ 1 row in set (0.00 sec)
步骤三：找出高于此薪资的
注意：比普通员工薪资还要高的一定是领导
select ename from emp where sal>(select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));
+-------+ | ename | +-------+ | JONES | | BLAKE | | CLARK | | SCOTT | | KING | | FORD | +-------+ 6 rows in set (0.00 sec)
9、取得薪水最高的前五名员工
mysql> select ename,sal from emp order by sal desc limit 5; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+ 5 rows in set (0.00 sec)
10、取得薪水最高的第六到第十名员工
mysql> select ename,sal from emp order by sal desc limit 5,5; //第一个参数表示开始的索引，默认从0开始 //第二个参数表示长度 +--------+---------+ | ename | sal | +--------+---------+ | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | MARTIN | 1250.00 | +--------+---------+ 5 rows in set (0.00 sec)
11、取得最后入职的 5 名员工
mysql> select ename,hiredate from emp order by hiredate desc limit 5; +--------+------------+ | ename | hiredate | +--------+------------+ | ADAMS | 1987-05-23 | | SCOTT | 1987-04-19 | | MILLER | 1982-01-23 | | FORD | 1981-12-03 | | JAMES | 1981-12-03 | +--------+------------+ 5 rows in set (0.00 sec)
12、取得每个薪水等级有多少员工
步骤一：获取每个员工薪资的等级
select e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+---------+-------+ | sal | grade | +---------+-------+ | 800.00 | 1 | | 1600.00 | 3 | | 1250.00 | 2 | | 2975.00 | 4 | | 1250.00 | 2 | | 2850.00 | 4 | | 2450.00 | 4 | | 3000.00 | 4 | | 5000.00 | 5 | | 1500.00 | 3 | | 1100.00 | 1 | | 950.00 | 1 | | 3000.00 | 4 | | 1300.00 | 2 | +---------+-------+ 14 rows in set (0.00 sec)
步骤二：计数
select s.grade, count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;
+-------+----------+ | grade | count(*) | +-------+----------+ | 1 | 3 | | 2 | 3 | | 3 | 2 | | 4 | 5 | | 5 | 1 | +-------+----------+ 5 rows in set (0.00 sec)
