获取有奖金的员工相关信息

1.使用含有关键字exists查找未分配具体部门的员工的所有信息

 

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

法1：用exists：
select * from employees e
where not exists
(select emp_no from dept_emp d where d.emp_no = e.emp_no);

 

法2：用left
select * from employees e
left join dept_emp d on d.emp_no = e.emp_no
where d.emp_no is null;

 

法3：用in

select *

from employees

where emp_no not in

(

    select emp_no

    from dept_emp

)

2.现有员工表employees如下：

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21

有员工奖金表emp_bonus:

emp_no	recevied	btype
10001	2010-01-01	1
10002	2010-10-01	2

有薪水表salaries:

emp_no	salary	from_date	to_date
10001	60117	1986-06-26	1987-06-26
10001	62102	1987-06-26	1988-06-25
10001	66074	1988-06-25	1989-06-25
10001	66596	1989-06-25	1990-06-25
10001	66961	1990-06-25	1991-06-25
10001	71046	1991-06-25	1992-06-24
10001	74333	1992-06-24	1993-06-24
10001	75286	1993-06-24	1994-06-24
10001	75994	1994-06-24	1995-06-24
10001	76884	1995-06-24	1996-06-23
10001	80013	1996-06-23	1997-06-23
10001	81025	1997-06-23	1998-06-23
10001	81097	1998-06-23	1999-06-23
10001	84917	1999-06-23	2000-06-22
10001	85112	2000-06-22	2001-06-22
10001	85097	2001-06-22	2002-06-22
10001	88958	2002-06-22	9999-01-01
10002	72527	1996-08-03	1997-08-03
10002	72527	1997-08-03	1998-08-03
10002	72527	1998-08-03	1999-08-03
10002	72527	1999-08-03	2000-08-02
10002	72527	2000-08-02	2001-08-02
10002	72527	2001-08-02	9999-01-01

• 其中bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 to_date='9999-01-01'表示当前薪水。
• 请你给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。
• bonus结果保留一位小数，输出结果按emp_no升序排序。

以上数据集的输出结果如下:

emp_no	first_name	last_name	btype	salary	bonus
10001	Georgi	Facello	1	88958	8895.8000
10002	Bezalel	Simmel	2	72527	14505.4000

解法1：

#不要忘记筛选条件，工资要求是当前工资

select e.emp_no,first_name,last_name,btype,salary,(case

when btype=1 then salary*0.1

when btype=2 then salary*0.2

else salary*0.3

end) as bonus

from employees e

inner join emp_bonus b on e.emp_no=b.emp_no

inner join salaries s on e.emp_no=s.emp_no

where s.to_date='9999-01-01';

 

解法2：

首先，理解题意，这道题的输出很明确，前五个字段emp_no,first_name,last_name,btype,salary很容易获取，所以这道题的题意很明确，你怎么给我算出bonus，并把这几个字段放在一个表中。第一步怎么求bonus，当是用salaries表连接bonus表，使用case语句来求了，如下：

1 2 3 4 5 6 7 8 9

select s.emp_no,btype,salary,( case btype               // 用case查询对应的bonus when 1 then salary*0.1  when 2 then salary*0.2 else salary*0.3 end) bonus from salaries s,emp_bonus eb where s.emp_no=eb.emp_no and s.to_date='9999-01-01'

第二步求出了case之后，设上面的查询结果为表 L，那么表L连接表employees，连接条件为emp_no，就可以查出来所有的东西了。如下：

select
e.emp_no,e.first_name,e.last_name,
l.btype,l.salary,l.bonus
from employees e
inner join L on l.emp_no=e.emp_no;

 

解法3：

select e.emp_no,first_name,last_name,btype,salary,

        bonus_rate*salary as bonus

from employees e

join

(

    select eb.emp_no,eb.btype,ns.salary,

    (case when btype = 1 then 0.1

     when btype = 2 then 0.2

     else 0.3 end) as bonus_rate

from

    emp_bonus eb

join

    (select emp_no,salary from salaries

    where to_date = '9999-01-01') ns

where eb.emp_no = ns.emp_no

) t

where e.emp_no = t.emp_no

或者

 

select e.emp_no,first_name,last_name,btype,salary,bonus

from employees e

join

(

    select eb.emp_no,eb.btype,ns.salary,

    (case when btype = 1 then 0.1*salary

     when btype = 2 then 0.2*salary

     else 0.3*salary end) as bonus

from

    emp_bonus eb

join

    (select emp_no,salary from salaries

    where to_date = '9999-01-01') ns

where eb.emp_no = ns.emp_no

) t

where e.emp_no = t.emp_no

 

注意：严格来说需要用round保留一位小数