SQL图解面试题：如何分析员工奖金？（多表连接、case when）

【题目】

雇员表中是员工的基本信息

雇员奖金表是给员工发放奖金的记录，其中奖金有3种类型：

奖金类型的值是1，表示奖金金额为薪水salary的10%，

奖金类型的值是2，表示奖金金额为薪水salary的20%，

奖金类型的值是3，表示奖金金额为薪水salary的30%

薪水表是员工的薪水

 

问题：查找雇员编号、名、姓、奖金类型、对应的当前薪水以及奖金金额。

【解题思路】

我们首先观察输出格式要求：

雇员编号三个表都有，名和姓来自雇员表，奖金类型来自雇员奖金表，薪水来自薪水表，奖金金额与奖金类型和薪水相关。

1.多表联结

由上述条件我们可以看出需要用到多表联结，将三个表通过雇员编号联结起来。

我们先将雇员表和雇员奖金表联结，联结结果作为临时表。

 

 然后再将该临时表与薪水表进行联结，同样保留两个表的公共数据，用内联结。 

 

select *
from 雇员表 e inner join 雇员奖金表 b
on e.雇员编号 = b.雇员编号
inner join 薪水表 s
on b.雇员编号 = s.雇员编号;

 2.选出分析需要的列

选出输出格式需要的列，在select子句中指定列名。

select s.雇员编号,e.名,e.姓,b.奖金类型,s.薪水
from 雇员表 e inner join 雇员奖金表 b
on e.雇员编号 = b.雇员编号
inner join 薪水表 s
on b.雇员编号 = s.雇员编号;

 

 

3.查找当前薪水

查找当前薪水，也就是满足结束日期='9999-01-01'的行，在where子句中加上条件。

select s.雇员编号,e.名,e.姓,b.奖金类型,s.薪水
from 雇员表 e inner join 雇员奖金表 b
on e.雇员编号 = b.雇员编号
inner join 薪水表 s
on b.雇员编号 = s.雇员编号
where s.结束日期 = '9999-01-01';

 

4.奖金金额

奖金金额分成三种情况：

奖金类型的值是1，表示奖金金额为薪水salary的10%，

奖金类型的值是2，表示奖金金额为薪水salary的20%，

奖金类型的值是3，表示奖金金额为薪水salary的30%

多条件的问题，要想到《猴子 从零学会SQL》里讲过的用case表达式实现。

 

select s.雇员编号,e.名,e.姓,b.奖金类型,s.薪水,
(case b.奖金类型
when 1 then s.薪水*0.1
when 2 then s.薪水*0.2
else s.薪水*0.3 
end) as 奖金金额
from 雇员表 e inner join 雇员奖金表 b
on e.雇员编号 = b.雇员编号
inner join 薪水表 s
on b.雇员编号 = s.雇员编号 
where s.结束日期 = '9999-01-01';

 

【本题考点】

1.涉及到多个表的情况，要想到用多表联结。

2.多条件的业务问题，要想到用case表达式。