mysql多表查询笔试题_MySQL多表查询[3题]

原始表如下：

emp

dept

第1题，3.4查找只存在于一个表中的数据(差集)

例：找出在dept表中存在，而在emp表中不存在的部门编号(如果有的话)

需要最终结果：

答：

#方法一：

select distinct deptno

from dept

where deptno not in (select deptno from emp)

#方法二：

select deptno

from dept d

where not exists (select null

from emp e

where e.deptno = d.deptno)

(注意此处方法一用not in的原因是因为emp表中deptno没有空值，如果有的话，就只能用方法二，否则not in()中包含null将查不到任何值)

延伸：如果要查找存在于A但不存在于B & 存在于B但不参在于A的内容，可以使用上述代码2次，并做union all 处理

第2题，3.10组合使用外连接查询与聚合函数

例：计算出部门编号为10的员工的工资总额以及奖金总额

(type=1,奖金=工资*0.1；type=2,奖金=工资*0.2)

奖金表如下：

emp_bonus

需要最终结果：

答：

#1.将两表连接起来，找出需要的字段

create table d1 as

select e.empno, e.ename, e.sal, e.deptno,

e.sal * case when eb.type is null then 0

when eb.type =1 then 0.1

when eb.type =2 then 0.2

end as bonus

from emp e left join emp_bonus eb

on e.empno = eb.empno

where e.deptno = 10

#2.做相应的聚合运算，sal去重避免重复计算，bonus做加总

create table d2 as

select empno, avg(sal) sal, sum(bonus) bonus from d1

group by empno

#3.最后加总显示所需要的结果

create table d3 as

select 10 deptno, sum(sal) sal_total, sum(bonus) bonus_total from d2

得到：

步骤1

步骤2

步骤3

第3题，3.12在运算和比较中使用null

例：找出emp表中业务提成(comm列)比员工WARD低的所有员工

需要最终结果：

答：

select ename, comm

from emp

where coalesce(comm,0) < (select comm from emp where ename='WARD')

用coalesce()处理null值，如果不处理的话，只能返回符合条件有数字的行