Mysql练习

1.求每个部门最高薪水的人员名称

解决思路：1 先求每个部门最高的薪水

SELECT
e.deptno,MAX(e.sal) as maxsal
from
emp e
GROUP BY
e.deptno;

2. 将第一步得出来的结果，当成临时表

SELECT
e.deptno,ename,t.maxsal,e.sal
from
(SELECT
e.deptno,max(e.sal) as maxsal
from
emp e
GROUP BY
e.deptno) t
JOIN
emp e
on
t.deptno = e.deptno
WHERE
t.maxsal = e.sal
ORDER BY --做一个排序
e.deptno;

SELECT
e.deptno,avg(e.sal) as avgsal
from
emp e
GROUP BY
e.deptno;

2. 查看哪些人的薪水大于平均水平

SELECT
e.deptno,e.ename,e.sal
from
(SELECT
e.deptno,avg(e.sal) as avgsal
from
emp e
GROUP BY
e.deptno) t
JOIN
emp e
ON
e.deptno = t.deptno
WHERE
t.avgsal < e.sal

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

SELECT
e.deptno,avg(e.sal) as avgsal
from
emp e
GROUP BY
e.deptno;

SELECT
t.deptno,t.avgsal,s.grade
from
( SELECT
e.deptno,avg(e.sal) as avgsal
from
emp e
GROUP BY
e.deptno) t
JOIN
salgrade s
on
t.avgsal BETWEEN s.losal and hisal

SELECT
e.deptno,e.ename,s.grade
FROM
emp e
join
salgrade s
on
e.sal BETWEEN s.losal and s.hisal;

2. 在临时表求平均的

SELECT
t.deptno,t.ename,AVG(t.grade) as avggrade
FROM
(SELECT
e.deptno,e.ename,s.grade
FROM
emp e
join
salgrade s
on
e.sal BETWEEN s.losal and s.hisal) t
GROUP BY
t.deptno

SELECT e.sal from emp e ORDER BY e.sal desc LIMIT 1

SELECT a.sal FROM emp a WHERE a.sal not in (SELECT
a.sal
from
emp a
join
emp b
on
a.sal < b.sal);

SELECT
e.deptno,avg(e.sal) as avgsal
FROM
emp e
GROUP BY
e.deptno

2. 求出平均水平最大值，再进行筛选

SELECT
t.deptno
FROM
( SELECT
e.deptno,avg(e.sal) as avgsal
FROM
emp e
GROUP BY
e.deptno)t
HAVING
MAX(avgsal)

7 . 求取平均薪水最高部门的部门名称

SELECT
d.dname
FROM
( SELECT
e.deptno,avg(e.sal) as avgsal
FROM
emp e
GROUP BY
e.deptno)t
JOIN
dept d
ON
t.deptno = d.deptno
HAVING
MAX(avgsal)

8.求平均薪水的等级最低的部门名称

SELECT
e.deptno,avg(e.sal) AS avgsal
FROM
emp e
group by
e.deptno

SELECT
MIN(s.grade)
from
(SELECT
e.deptno,avg(e.sal) AS avgsal
FROM
emp e
group by
e.deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal

3.连表就行求部门名称

SELECT
t.deptno,t.dname,s.grade
FROM
(SELECT
e.deptno,d.dname,avg(e.sal) as avgsal
FROM
emp e
JOIN
dept d
on
e.deptno = d.deptno
GROUP BY
e.deptno,d.dname)t
JOIN
salgrade s
on
t.avgsal BETWEEN s.losal and s.hisal
WHERE
s.grade = (SELECT
MIN(s.grade)
from
(SELECT
e.deptno,avg(e.sal) AS avgsal
FROM
emp e
group by
e.deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal )

SELECT
DISTINCT --去重
mgr
FROM
emp

SELECT
max(sal) as maxsal
FROM
emp
WHERE
empno not in (SELECT
DISTINCT
mgr
FROM
emp
WHERE mgr is not null)

注 in 会自动忽略空值，not in不会自动忽略

SELECT ename from emp WHERE sal > (SELECT
max(sal) as maxsal
FROM
emp
WHERE
empno not in (SELECT
DISTINCT
mgr
FROM
emp
WHERE mgr is not null)
)

10 取薪水最高的前五名员工

SELECT * from emp
ORDER BY
sal desc
LIMIT
0,5;

11 取得薪水最高的第六到第十名员工

SELECT * from emp ORDER BY sal desc limit 5,5

12 取得最后入职的5名员工

SELECT * from emp order by hiredate desc limit 0,5

13求每个等级有多少员工

1.查询每个员工的薪水等级

SELECT
e.ename,s.grade
FROM
emp e
JOIN
salgrade s
on
e.sal BETWEEN s.losal and s.hisal
ORDER BY
s.grade

SELECT
t.grade ,COUNT(t.ename) as countEmp
FROM
(SELECT
e.ename,s.grade
FROM
emp e
JOIN
salgrade s
on
e.sal BETWEEN s.losal and s.hisal
ORDER BY
s.grade) t
GROUP BY
t.grade

来源：https://www.cnblogs.com/sj1027523/p/14706011.html
图文来源于网络，如有侵权请联系删除。