mysql 子查询实践

数据集准备

这里选择大家比较熟悉的 Emp/Dept 表做为数据集

创建 emp 表的 DDL 语句，如下所示：

 CREATE TABLE IF NOT EXISTS  emp (
  EMPNO varchar(20) ,
  ENAME varchar(512) ,
  JOB varchar(512) ,
  MGR int ,
  HIREDATE datetime ,
  SAL double ,
  COMM double ,
  DEPTNO bigint );

创建 dept 表的 DDL 语句，如下所示：

CREATE TABLE IF NOT EXISTS dept (
  DEPTNO bigint ,
  DNAME varchar(512) ,
  LOC varchar(512));

插入emp 表数据

  

insert into emp values
('7369','SMITH','CLERK','7902','1980-12-17 00:00:00','800','','20'),
('7499','ALLEN','SALESMAN','7698','1981-02-20 00:00:00','1600','300','30'),
('7521','WARD','SALESMAN','7698','1981-02-22 00:00:00','1250','500','30'),
('7566','JONES','MANAGER','7839','1981-04-02 00:00:00','2975','','20'),
('7654','MARTIN','SALESMAN','7698','1981-09-28 00:00:00','1250','1400','30'),
('7698','BLAKE','MANAGER','7839','1981-05-01 00:00:00','2850','','30'),
('7782','CLARK','MANAGER','7839','1981-06-09 00:00:00','2450','','10'),
('7788','SCOTT','ANALYST','7566','1987-04-19 00:00:00','3000','','20'),
('7839','KING','PRESIDENT','','1981-11-17 00:00:00','5000','','10'),
('7844','TURNER','SALESMAN','7698','1981-09-08 00:00:00','1500','0','30'),
('7876','ADAMS','CLERK','7788','1987-05-23 00:00:00','1100','','20'),
('7900','JAMES','CLERK','7698','1981-12-03 00:00:00','950','','30'),
('7902','FORD','ANALYST','7566','1981-12-03 00:00:00','3000','','20'),
('7934','MILLER','CLERK','7782','1982-01-23 00:00:00','1300','','10')；

插入 dept 表数据

insert into dept values
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');

题目一：列出至少有一个员工的所有部门。

select * from dept where DEPTNO in (select distinct(DEPTNO) from emp);

题目二：列出薪金比 SMITH 多的所有员工。

select * from emp where SAL > (select max(SAL) from emp where ENAME= 'SMITH' );

  题目三：列出所有员工的姓名及其直接上级的姓名。

select a.ENAME ,b.ENAME as leader from emp a,emp b where a.MGR = b.EMPNO;

题目四：列出最低薪金大于 1500 的各种工作。

select JOB, min(SAL) as min_sal from emp  group by JOB having min_sal > 1500;

或

select * from (select JOB , min(SAL) as min_sal  from emp   group by JOB) a where a.min_sal>1500 ;

题目五：列出在每个部门工作的员工数量、平均工资。

select count(*) emp_number ,round(avg(SAL),2) as avg_sal,DEPTNO from emp group by DEPTNO;

题目六： 列出每个部门的薪水前3名的人员的姓名以及他们的名次（Top n 的需求非常常见）。

select * from emp a where 2>=(select count(*) as cnt from emp where a.DEPTNO = emp.DEPTNO and SAL > a.SAL) order by DEPTNO,SAL;

题目七： 用一个 SQL 写出每个部门的人数、CLERK（办事员）的人数占该部门总人数占比 。

select DEPTNO, count(*) emp_number ,round(sum(case when JOB = 'CLERK' then 1 else 0 end)/count(*),2) as rate from emp group by DEPTNO;