首页 > 试题广场 >

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

[编程题]获取有奖金的员工相关信息。

热度指数：171832 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 32M，其他语言64M
算法知识视频讲解

现有员工表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

输入

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

算法知识视频讲解

Mysql

一个好懒的人

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

发表于 2022-06-23 14:16:03 回复(0)

Mysql

重铸广师荣光

使用case when 即可
select t1.emp_no,first_name,last_name,btype,salary,case btype when 1 then 0.1*salary
                                                   when 2 then 0.2*salary
                                                   else 0.3* salary end 'bonus'
from employees t1 join emp_bonus t2 on 
t1.emp_no = t2.emp_no 
join salaries t3 on 
t1.emp_no = t3.emp_no
where to_date = '9999-01-01'
order by t1.emp_no

发表于 2022-05-15 16:27:54 回复(0)

Mysql

Stzolo

select a.emp_no, a.first_name, a.last_name, b.btype, c.salary,
case when b.btype = '1' then c.salary*0.1
when b.btype = '2' then c.salary*0.2
else c.salary*0.3 end as bonus
from employees a
join emp_bonus b on a.emp_no = b.emp_no
join salaries c on a.emp_no = c.emp_no and c.to_date = '9999-01-01'

发表于 2022-04-17 20:23:18 回复(0)

Hikvision_boy

WITH q1 AS
(select a.emp_no,a.first_name,a.last_name,b.btype,c.salary from salaries c join employees a on
a.emp_no=c.emp_no join emp_bonus b on b.emp_no=c.emp_no where to_date='9999-01-01' )
select *,case btype
when 1 then salary*0.1
when 2 then salary*0.2
else salary*0.3
end as bonus from q1;

发表于 2022-02-11 22:00:27 回复(0)

晓_

select
    e.emp_no,e.first_name,e.last_name,b.btype,s.salary,
    round(s.salary*r,1)
from
    employees e,
    (select *,btype/10.0 as r from emp_bonus) b,
    salaries s
where
    e.emp_no = b.emp_no
and
    e.emp_no = s.emp_no
and
    s.to_date='9999-01-01'

发表于 2021-07-09 10:15:02 回复(0)

一只scl

select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,(s.salary*eb.btype*0.1) as bonus
from employees e
inner join emp_bonus eb on e.emp_no=eb.emp_no
left join salaries s on e.emp_no=s.emp_no
where s.to_date='9999-01-01'

发表于 2020-09-13 11:04:50 回复(0)

covtind

SELECT a.emp_no,a.first_name,a.last_name,c.btype,d.salary,(d.salary*c.btype*0.1) bonus
FROM employees a,emp_bonus c,salaries d
WHERE a.emp_no=c.emp_no
AND c.emp_no=d.emp_no
AND d.to_date='9999-01-01'

发表于 2018-09-13 21:11:49 回复(0)

fightingfighting

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

发表于 2018-03-20 10:08:18 回复(0)

ciphersaw

本题主要考查 SQLite 中 CASE 表达式的用法。即当 btype = 1 时，得到 salary * 0.1；当 btype = 2 时，得到 salary * 0.2；其他情况得到 salary * 0.3。详细用法请参考：

http://www.sqlite.org/lang_expr.html 中的【The CASE expression】

http://www.2cto.com/database/201202/120267.html 中的【条件表达式】

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 
(CASE b.btype 
 WHEN 1 THEN s.salary * 0.1
 WHEN 2 THEN s.salary * 0.2
 ELSE s.salary * 0.3 END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

其实观察测试数据会发现 btype 只有1,2,3三种情况，即使不会 CASE 表达式，也能运用四则运算解出：（注意要除以10.0，如果除以10的话，结果的小数位会被舍去）

SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, 
(s.salary * b.btype / 10.0) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

发表于 2017-07-24 22:35:37 回复(26)

黄宗文

select t1.emp_no, t1.first_name, t1.last_name, t3.btype, t2.salary,
(case t3.btype
when 1 then t2.salary*0.1
when 2 then t2.salary*0.2
else t2.salary*0.3 end
) as bonus
from employees as t1
inner join salaries as t2
on t1.emp_no=t2.emp_no
and t2.to_date='9999-01-01'
inner join emp_bonus as t3
on t1.emp_no=t3.emp_no

;

-- 提高准确率，避免误操作

发表于 2020-06-30 23:18:11 回复(0)

DS奋斗中

SELECT em.emp_no,em.first_name,em.last_name,eb.btype,sa.salary,(sa.salary*0.1) AS bonus
FROM employees AS em,emp_bonus AS eb,salaries AS sa
WHERE em.emp_no=eb.emp_no
AND em.emp_no=sa.emp_no
AND sa.to_date='9999-01-01'
AND eb.btype=1
UNION
SELECT em.emp_no,em.first_name,em.last_name,eb.btype,sa.salary,(sa.salary*0.2) AS bonus
FROM employees AS em,emp_bonus AS eb,salaries AS sa
WHERE em.emp_no=eb.emp_no
AND em.emp_no=sa.emp_no
AND sa.to_date='9999-01-01'
AND eb.btype=2
UNION
SELECT em.emp_no,em.first_name,em.last_name,eb.btype,sa.salary,(sa.salary*0.3) AS bonus
FROM employees AS em,emp_bonus AS eb,salaries AS sa
WHERE em.emp_no=eb.emp_no
AND em.emp_no=sa.emp_no
AND sa.to_date='9999-01-01'
AND eb.btype!=1
AND eb.btype!=2
ORDER BY em.emp_no

不用case的情况可以分三次查询再将结果合并

发表于 2020-05-30 12:49:44 回复(0)

C/C++

卷积神经网络哦

btype为1是10% 为2是%20 为3是30% 这样直接用除法除以10就行了注意数据类型要用10.0
此题只需要用三个表连接起来即可

SELECT e.emp_no, e.first_name, e.last_name, 
eb.btype, s.salary, s.salary*(eb.btype/10.0) as bonus
FROM employees AS e INNER JOIN emp_bonus AS eb ON e.emp_no = eb.emp_no 
INNER JOIN salaries AS s ON s.emp_no = e.emp_no 
AND s.to_date = '9999-01-01'

发表于 2019-05-13 11:28:45 回复(7)

此用户名涉嫌违规

方法一（case）：

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"

case语法：

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
对于这道题，个人认为用case解答是最符合题意的。直接按条件选出奖金数额。

方法二（观察数据）：

select e.emp_no,
    e.first_name,
    e.last_name,
    eb.btype,
    s.salary,
    s.salary*eb.btype*0.1 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"

如果观察测试案例的数据，可以发现规律btype类型为1,2,3时奖金分别是工资的0.1,0.2,0.3。所以按照这个规律可写出以上的mysql语句。但是题中表示的是其他类型均为薪水的30%。bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。所以换个btype为4的测试案例就通不过了

发表于 2021-03-26 11:06:17 回复(1)

qstalking

#按照奖金类型和工资发奖金

思路1:题目可以发现奖金类型与奖金比例的关系是btype*0.1，所以可以直接计算。（这里要用*0.1，不能用/10，否则会报错）

select e.emp_no
        ,e.first_name
        ,e.last_name
        ,b.btype
        ,s.salary
        ,(s.salary*b.btype*0.1) as bonus
from employees e
join salaries s on e.emp_no=s.emp_no
join emp_bonus b on e.emp_no=b.emp_no
where s.to_date='9999-01-01';

思路2:用case进行分类。

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

思路3:先把每个奖金类型的分别按输出格式查出来，最后用union拼接。

发表于 2021-01-25 15:22:59 回复(6)

风的方向标

select e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
(case b.btype
when 1 then s.salary * 0.1
when 2 then s.salary * 0.2
when 3 then s.salary * 0.3 end) as bonus
from employees e inner join emp_bonus b on e.emp_no = b.emp_no
inner join salaries as s on e.emp_no = s.emp_no
where b.btype is not null

and s.to_date = '9999-01-01'

整合了一下回答，这个是更加清晰的解题步骤

在emp_bonus表中，有些员工是不含btype的，此时对应的btype为null

所以case函数里面没有使用else分支，

并且用where子句排除了无bonus的员工

相较于把b.bypte is not null 放在from 子句中会更加清楚

发表于 2020-08-03 08:23:59 回复(4)

小陈也只是想要一个实习罢了

#不要忘记筛选条件，工资要求是当前工资
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';

发表于 2020-06-06 22:27:31 回复(1)

Mysql

Qiuxj

SELECT e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
IF(btype=1, 0.1*s.salary, IF(btype=2, 0.2*s.salary, 0.3*s.salary)) as bonus
FROM employees e, emp_bonus eb, salaries s
WHERE e.emp_no=eb.emp_no and e.emp_no=s.emp_no and s.to_date='9999-01-01';

发表于 2021-10-08 10:20:20 回复(0)

Mysql

Lion灬Heart

# 本体考察case when 和三表连接的使用，虽然也可以不使用case when ，***alary * b.btype / 10.0) AS bonus（题目中的规律）。但是实际的大多数业务情况都是用case when，因为 btype 与奖金的关系是不规律的

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

发表于 2021-09-30 15:26:05 回复(0)

凌12

本题主要是考查对case表达式的用法，由上面列出来的字段可以知道dept_emp这个表是没有用到的。

具体的使用方法可以参考：https://blog.csdn.net/rongtaoup/article/details/82183743

select employees.emp_no,employees.first_name,employees.last_name,emp_bonus.btype,salaries.salary,
(case emp_bonus.btype 
when 1 then salaries.salary*0.1
when 2 then salaries.salary*0.2
when 3 then salaries.salary*0.3 end
)from employees
inner join emp_bonus on employees.emp_no=emp_bonus.emp_no
inner join salaries on employees.emp_no=salaries.emp_no
and salaries.to_date='9999-01-01'

发表于 2021-08-02 15:37:17 回复(0)

Tina要成为数据分析师呀

考察case when的用法，注意其中要限制btype is not null

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

发表于 2020-12-22 20:49:35 回复(0)