牛客——SQL253 获取有奖金的员工相关信息

描述

现有员工表employees如下：

有员工奖金表emp_bonus:

有薪水表salaries:

• 其中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升序排序。

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

示例1:

drop table if exists  `employees` ; 
drop table if exists  emp_bonus; 
drop table if exists  `salaries` ; 
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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
insert into emp_bonus values
(10001, '2010-01-01',1),
(10002, '2010-10-01',2);
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');

INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');

输出：

10001|Georgi|Facello|1|88958|8895.8
10002|Bezalel|Simmel|2|72527|14505.4

我的解题思路：

这题比较简单，主要考察表连接，相同字段可以使用using连接

1.找出员工的当前薪水

select emp_no,
       salary
from salaries
where to_date = '9999-01-01'

2.表内连接，之后使用case when 求出奖金

case e2.btype
   when 1 then round(salary * 0.1, 1)
   when 2 then round(salary * 0.2, 1)
   else round(salary * 0.3, 1) end as bonus

完整代码：

select e1.emp_no,
       first_name,
       last_name,
       e2.btype,
       e3.salary,
       case e2.btype
           when 1 then round(salary * 0.1, 1)
           when 2 then round(salary * 0.2, 1)
           else round(salary * 0.3, 1) end as bonus
from employees e1
         inner join emp_bonus e2 using (emp_no)
         inner join (select emp_no,
                           salary
                    from salaries
                    where to_date = '9999-01-01') e3
                   using (emp_no)
;

本栏目推荐文章
• openpyxl模块---------------------------------------------提取身份证信息及计算年龄
• SQL Server 检测是不是数字型数据的两种方法
• SQL Join的一些总结
• SQL Server 高性能写入的一些总结
• 网络攻击技术开篇——SQL Injection
• JFinal学生信息管理系统
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 初中信息奥赛模拟测试
• SQL SERVER日期时间转字符串
• sql server 保留2位小数

奖金 员工 信息 SQL 253奖金 员工 信息sql 奖金 员工577 奖金 员工mysql 577 工资 员工 部门sql 员工 账号 信息 员工 信息 员工 收入 经理sql 员工 信息001 员工 部门1789 sql 253