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

with te as (
    select sa.emp_no,sa.salary, em.first_name ,em.last_name from salaries sa, employees em
    where em.emp_no = sa.emp_no and  sa.to_date='9999-01-01'
)
select t.emp_no,t.first_name,t.t.last_name,bu.btype,t.salary,
CASE btype
        WHEN '1' THEN CAST(salary * 0.1 AS DECIMAL(10, 1))
        WHEN '2' THEN CAST(salary * 0.2 AS DECIMAL(10, 1))
        ELSE CAST(salary * 0.3 AS DECIMAL(10, 1))
    END AS bonus
from te t,emp_bonus bu where t.emp_no=bu.emp_no

1. 首先，我们需要创建一个名为te的临时表，用于存储员工的员工编号、名字、工资和部门类型。通过连接salaries和employees表，筛选出离职日期为'9999-01-01'的员工。

with te as (
    select sa.emp_no, sa.salary, em.first_name, em.last_name
    from salaries sa
    join employees em on em.emp_no = sa.emp_no
    where sa.to_date = '9999-01-01'
)

1. 接下来，我们需要根据部门类型计算员工的奖金。我们将te表与emp_bonus表连接，并根据部门类型（'1'代表部门类型1，'2'代表部门类型2，其他为部门类型3）计算奖金。

select t.emp_no, t.first_name, t.last_name, bu.btype, t.salary,
       CASE btype
           WHEN '1' THEN CAST(salary * 0.1 AS DECIMAL(10, 1))
           WHEN '2' THEN CAST(salary * 0.2 AS DECIMAL(10, 1))
           ELSE CAST(salary * 0.3 AS DECIMAL(10, 1))
       END AS bonus
from te t
join emp_bonus bu on t.emp_no = bu.emp_no

这个查询将返回一个结果集，其中包含员工的员工编号、名字、姓氏、部门类型、工资和奖金。