首页 > 试题广场 >

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

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

热度指数：174691 时间限制：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

刷牛客的coder很爱吃香菜

select
    e.emp_no,
    e.first_name,
    e.last_name,
    temp.btype,
    temp.salary,
    temp.bonus
from
    employees e
    inner join (
        select
            b.emp_no emp_no,
            b.btype btype,
            s.salary 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
            emp_bonus b
            inner join salaries s on b.emp_no = s.emp_no
        where
            s.to_date = '9999-01-01'
    ) temp on e.emp_no = temp.emp_no

先查出有奖金的员工，以及员工当前工资，奖金等级，对应的奖金数

最后再连接员工信息表，查出题目要求的字段

发表于 2025-01-07 11:41:10 回复(0)

Mysql

青衣白马少年时

select 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 bonus
from (select *
from employees
join salaries using(emp_no)
where to_date = '9999-01-01') s
join emp_bonus using(emp_no)
order by emp_no

在合表操作时先筛选了一下日期

发表于 2025-01-04 15:33:57 回复(0)

Mysql

牛客733099951号

select a.emp_no, a.first_name, a.last_name, b.btype, c.salary, round((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),1) as bonus

from employees a

inner join emp_bonus b

on a.emp_no = b.emp_no

inner join salaries c

on b.emp_no = c.emp_no

where c.salary in (select max(salary)

from salaries group by emp_no) and c.to_date in (select max(to_date) from salaries group by emp_no)

发表于 2024-10-20 11:39:21 回复(0)

Mysql

CBlock

select

e.emp_no,

e.first_name,

e.last_name,

b.btype,

s.salary,

(

case when b.btype = 1 then round(s.salary * 0.1,1)

when b.btype = 2 then round(s.salary * 0.2,1)

else round(s.salary * 0.3,1)

end

) as bonus

from employees e

join emp_bonus b

on e.emp_no = b.emp_no

join salaries s

on b.emp_no = s.emp_no

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

order by e.emp_no;

发表于 2024-09-30 16:27:40 回复(0)

Mysql

牛客188206174号

select e.emp_no, e.first_name, e.last_name, b.btype, s.salary,

round((case b.btype when 1 then 0.1*s.salary

when 2 then 0.2*s.salary

else 0.3*s.salary

end),1) bonus

from salaries s join employees e

on s.emp_no = e.emp_no

join emp_bonus b

on b.emp_no = e.emp_no

where s.to_date = '9999-01-01';

发表于 2024-09-24 07:45:37 回复(0)

Mysql

想从小菜狗变为程序媛

其中bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%

一般出现这种需要分段判断的首先要想到的就是case when，这里还需要注意一下是inner join 很多时候第一意识都是left join 虽然最终结果有的时候一致，但是根据题意inner join 会更准确。

SELECT

s.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
        salaries s
    inner JOIN emp_bonus eb ON s.emp_no = eb.emp_no
    inner JOIN employees e ON s.emp_no = e.emp_no
    where to_date = "9999-01-01"
    order by emp_no

发表于 2024-09-12 11:04:56 回复(0)

Mysql

一别两宽_

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

where to_date = '9999-01-01' order by a.emp_no

发表于 2024-08-19 17:31:13 回复(0)

Mysql

-暗号-

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

发表于 2024-08-16 14:29:09 回复(0)

Mysql

牛客276398119号

select
t1.emp_no
,t1.first_name
,t1.last_name
,t2.btype
,t3.salary
,round(t3.salary * if(t2.btype<3,t2.btype/10,0.3),1) as bonus
from
employees as t1
left join
emp_bonus as t2
on t1.emp_no = t2.emp_no
left join
(select emp_no , salary from salaries where to_date = "9999-01-01") as t3
on t1.emp_no = t3.emp_no
where
btype is not null
order by
1

发表于 2024-08-07 23:54:49 回复(0)

Mysql

牛客689711920号

select rn.emp_no, e.first_name, e.last_name, rn.btype, rn.salary, rn.bonus

from (select b.emp_no, b.btype, s. salary,

CASE

WHEN b.btype in (1,2) THEN round(s.salary * b.btype / 10,1)

ELSE round(s.salary * 0.3,1)

END AS bonus

from emp_bonus b left join salaries s

on b.emp_no = s.emp_no

where s.to_date = '9999-01-01') rn

left join employees e on rn.emp_no = e.emp_no

order by emp_no

发表于 2024-07-30 12:12:16 回复(0)

Mysql

在提需求的追梦人

记下：

如果需要用round()做四舍五入，整数乘以百分数以后再round()会报错，整数乘以小数以后再round()不会报错。

select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,
    case
        when eb.btype = 1 then round(s.salary*0.1,1)
        when eb.btype = 2 then round(s.salary*0.2,1)
        else round(s.salary*0.3,1) 
    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
    				and s.to_date = '9999-01-01'
order by emp_no;

发表于 2024-07-19 13:42:01 回复(0)

Mysql

饶我狗命

select 
    e.emp_no,e.first_name,e.last_name, 
    b.btype,s.salary,
    case b.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 e join emp_bonus b on e.emp_no = b.emp_no join (select * from salaries where to_date = '9999-01-01') s on b.emp_no = s.emp_no order by e.emp_no;

发表于 2024-06-13 21:38:51 回复(0)

Mysql

SummerSue

select
    e.emp_no,
    e.first_name,
    e.last_name,
    eb.btype,
    s.salary,
    round(
            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,
            4
        ) bonus
from employees e join salaries s
on e.emp_no=s.emp_no
join emp_bonus eb
on e.emp_no=eb.emp_no
where s.to_date='9999-01-01'
order by e.emp_no

发表于 2024-06-10 17:31:37 回复(0)

Mysql

翻斗花园第一狙击手

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

发表于 2024-05-06 21:16:19 回复(1)

Mysql

FMW

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

发表于 2024-04-03 18:08:49 回复(0)

Mysql

俊朗的小章鱼不要葱花

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

发表于 2024-03-30 14:14:45 回复(1)

Mysql

昱乎昼

select distinct
    e.emp_no,
    e.first_name,
    e.last_name,
    eb.btype,
    s.salary,
    round(s.salary * eb.btype * 0.1, 1) 'bonus'
from
    employees e
    right join emp_bonus eb on e.emp_no = eb.emp_no
    left join salaries s on e.emp_no = s.emp_no
where
    to_date = '9999-01-01'
order by
    e.emp_no asc;

发表于 2024-03-26 16:57:05 回复(0)

Mysql

Jaygee1124

select
    e.emp_no,
    e.first_name,
    e.last_name,
    eb.btype,
    s.salary,
    round(
        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,
        1
    ) 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'
order by
    e.emp_no asc

发表于 2024-03-25 14:29:24 回复(0)