题解 | #获取有奖金的员工相关信息。#

/*
SELECT eb.emp_no, first_name, last_name, btype, salary, salary*0.1 as bonus
FROM employees as e, salaries as s, emp_bonus as eb
WHERE EXISTS(select emp_no from emp_bonus as eb
             where btype=1)
and e.emp_no=eb.emp_no
and s.emp_no = eb.emp_no
and e.emp_no=s.emp_no
and to_date='9999-01-01'
UNION
SELECT eb.emp_no, first_name, last_name, btype, salary, salary*0.2 as bonus
FROM employees as e, salaries as s, emp_bonus as eb
WHERE EXISTS(select emp_no from emp_bonus as eb
             where btype=2)
and e.emp_no=eb.emp_no
and s.emp_no = eb.emp_no
and e.emp_no=s.emp_no
and to_date='9999-01-01'
UNION
SELECT e.emp_no, first_name, last_name, btype, salary, salary*0.3 as bonus
FROM employees as e, salaries as s, emp_bonus as eb
WHERE EXISTS(select emp_no from emp_bonus as eb
            WHERE btype not in (1,2))
and e.emp_no=eb.emp_no
and s.emp_no = eb.emp_no
and e.emp_no=s.emp_no
and to_date='9999-01-01';
*/

select e.emp_no,
    e.first_name,
    e.last_name,
    eb.btype,
    s.salary,
    (
        case when eb.btype=1  then s.salary*0.1
           when eb.btype=2 then s.salary*0.2
           else s.salary*0.3
        end
     ) as bonus 
from employees e join emp_bonus eb on e.emp_no=eb.emp_no
     join salaries s on eb.emp_no=s.emp_no
where s.to_date="9999-01-01"

这道题的难点，还是在于自己不会这个函数，不知道自己不知道。于是想要用一种比较麻烦的方法来解决，最后还是不得不求助于他人。

上面省略的题目是错误的，原因在于UNION链接的双方必须具有完全一致的名称、表达式和聚合函数。