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

WITH t1 AS (
    SELECT *
    FROM salaries
    WHERE to_date = '9999-01-01'
    AND emp_no IN (SELECT emp_no FROM emp_bonus)
),
t2 AS (
    SELECT 
        emp_no,
        first_name,
        last_name
    FROM 
    employees
    WHERE emp_no IN (SELECT emp_no FROM emp_bonus)
),
t3 AS (
    SELECT 
        t2.emp_no,
        t2.first_name,
        t2.last_name,
        e.btype,
        t1.salary,
        CASE WHEN e.btype = 1
            THEN ROUND(t1.salary * 0.1, 1)
             WHEN e.btype = 2
            THEN ROUND(t1.salary * 0.2, 1)
            ELSE ROUND(t1.salary * 0.3, 1)
        END AS bonus
    FROM t2
    LEFT JOIN t1
    ON t2.emp_no = t1.emp_no
    LEFT JOIN emp_bonus e
    ON t2.emp_no = e.emp_no
    ORDER BY t2.emp_no ASC
)
SELECT * FROM t3;