本文通过实例演示了如何使用MySQL查询每个薪水等级的员工数量、员工与其领导的关系以及受雇日期早于其直接上级的员工信息。通过表的自连接、外连接以及子查询，展示了一系列复杂的SQL操作，包括员工信息、部门信息的综合查询。
12、取得每个薪水等级有多少员工
第一步：找出每个员工的薪水等级
mysql> SELECT
-> e.ename,
-> s.grade
-> FROM
-> emp e
-> JOIN
-> salgrade s
-> ON
-> e.sal BETWEEN s.losal AND s.hisal;
+--------+-------+
| ename | grade |
+--------+-------+
| SMITH | 1 |
| JAMES | 1 |
| ADAMS | 1 |
| WARD | 2 |
| MARTIN | 2 |
| MILLER | 2 |
| TURNER | 3 |
| ALLEN | 3 |
| JONES | 4 |
| BLAKE | 4 |
| CLARK | 4 |
| SCOTT | 4 |
| FORD | 4 |
| KING | 5 |
+--------+-------+
第二步：在以上结果的基础上，按照grade分组计数
mysql> 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 |
+-------+----------+
13、列出所有员工及领导的姓名
emp a 员工表
emp b 领导表
进行表的自连接
mysql> SELECT
-> a.ename AS empname,
-> b.ename AS leadername
-> FROM
-> emp a
-> LEFT OUTER JOIN
-> emp b
-> ON
-> a.mgr = b.empno;
+---------+------------+
| empname | leadername |
+---------+------------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
最低0.47元/天 解锁文章
2903
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
