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;

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

1. 求出每個部門的平均薪水

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.取得部門中（所有人的）平均薪水等級

1. 取得部門中所有人的平均薪水的等級

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

2. 取得部門中所有人的平均的薪水等級

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

4. 取得部門中所有人的平均的薪水等級

1. 求出每個人的薪水等級

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

5. 不用組函式（MAX）,求取最高薪水（倆種方案）

1. 用倒敘，找第一行

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

2. 建倆張表，交叉進行比較去掉最大的，在找出不在倆表生成的表中數，就是最大的數

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);

6. 求取平均薪水最高的部門的部門編號

1. 求部門平均水平

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.求平均薪水的等級最低的部門名稱

1. 部門的平均薪水

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

2. 在一的基礎上求最低的部門薪資等級

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 )

9. 求比普通員工的最高薪水高的經理名字

1. 找出mgr的人有哪些

SELECT  
     DISTINCT   --去重
     mgr 
FROM
    emp 

2. 找出最高薪水員工

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

2. 將每個等級的員工計數

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

標籤：MySQL

上一篇：實時計算框架：Spark集群搭建與入門案例

下一篇：mybatis使用oracle進行添加資料的心得