首页 > 试题广场 >

查找在职员工自入职以来的薪水涨幅情况

[编程题]查找在职员工自入职以来的薪水涨幅情况
  • 热度指数：573299 时间限制：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 2001-06-22
10002
 1964-06-02
 Bezalel    
 Simmel    
  F 1999-08-03

有一个薪水表salaries简况如下:
emp_no 
 salary
 from_date 
 to_date
10001
 85097 2001-06-22
 2002-06-22
10001 88958
 2002-06-22 9999-01-01
10002
 72527 1999-08-03
 2000-08-02
10002
 72527 2000-08-02 2001-08-02

请你查找在职员工自入职以来的薪水涨幅情况(注意这里强调的是在职员工)，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为
（注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01'时，表示依然在职，无后续调整记录）
emp_no growth
10001 3861
示例1

输入

drop table if exists  `employees` ; 
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 `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 employees VALUES(10001,'1953-09-02','Georgi','Facello','M','2001-06-22');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1999-08-03');
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,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');

输出

emp_no|growth
10001|3861
算法知识视频讲解
添加回答
Mysql
清影LC头像 清影LC
SELECT emp_no, MAX(CASE WHEN to_date = '9999-01-01' THEN salary END)
- MIN(salary) AS growth
FROM salaries
GROUP BY emp_no
HAVING max(to_date = '9999-01-01')
ORDER BY growth ASC;
发表于 今天 16:35:17 回复(0)
Mysql
halosun头像 halosun
直接用salaries表，找to_date = '9999-01-01'的员工，再找这些员工的最后一条工资 - 第一条工资
select distinct
    emp_no,
    last_value (salary) over (partition by emp_no order by to_date rows between UNBOUNDED preceding and unbounded following) - first_value (salary) over (partition by emp_no order by to_date rows between UNBOUNDED preceding and unbounded following) as growth
from
    salaries
where
    emp_no in (select emp_no from salaries where to_date = '9999-01-01')
;

发表于 2025-04-11 17:17:16 回复(0)
Mysql
kukuju头像 kukuju
分别找出入职时的工资和现在的工资
with t1 as(
select b.emp_no,b.salary
from employees a
left join  salaries b
on a.emp_no=b.emp_no
where a.hire_date=b.from_date
)
,t2 as(
    select emp_no
,salary
from salaries
where to_date='9999-01-01'
)

select t2.emp_no, (t2.salary-t1.salary) as growth
from t1,t2
where t1.emp_no=t2.emp_no
order by growth

发表于 2025-04-09 15:05:46 回复(0)
Mysql
Yiiifan头像 Yiiifan
select
    ef.emp_no,
    (t_salary - f_salary) as growth
from
    (
        select
            e.emp_no,
            salary t_salary
        from
            employees e
            join salaries s on e.emp_no = s.emp_no
        where
            s.to_date = '9999-01-01'
    ) ef
    left join (
        select distinct
            e.emp_no,
            salary f_salary
        from
            employees e
            join salaries s on e.emp_no = s.emp_no
        where
            (s.emp_no, s.from_date) in (
                select
                    emp_no,
                    min(from_date)
                from
                    salaries
                group by
                    emp_no
            )
    ) et on ef.emp_no = et.emp_no
order by
    growth
新手写的
发表于 2025-04-01 11:40:59 回复(0)
Mysql
FadMemory头像 FadMemory 
select e.emp_no, max(salary)-min(salary) as growth
from employees e
join salaries s
using(emp_no)
where e.hire_date=s.from_date or s.to_date='9999-01-01'
group by emp_no
having max(s.to_date)='9999-01-01'
order by growth
发表于 2025-03-24 00:38:08 回复(0)
Mysql
牛客1008_头像 牛客1008_
select * from
(

SELECT
    t6.emp_no, (salary_1 - salary_0) AS growth #此处select emp_no需交代表名，用来区分emp_no具体来自哪张表。
FROM

(
    -- 初始工资表
    SELECT
        emp_no, salary AS salary_0
    FROM
    (
        SELECT
            *,
            DENSE_RANK() OVER (PARTITION BY emp_no ORDER BY to_date) AS a_rank
        FROM
            salaries
    ) t3
    WHERE
        a_rank = 1
) t4

right JOIN

(
    -- 涨薪后工资表
    SELECT
        emp_no, salary AS salary_1
    FROM
    (
        SELECT
            *,
            DENSE_RANK() OVER (PARTITION BY emp_no ORDER BY to_date DESC) AS t_rank
        FROM
            salaries
    ) t5
    WHERE
        t_rank = 1 and to_date='9999-01-01'
) t6

ON
    t4.emp_no = t6.emp_no

)t7

order by growth
;

发表于 2025-03-17 16:55:00 回复(0)
Mysql
顺顺顺利_3头像 顺顺顺利_3 
select b.emp_no,max(a.salary)-min(a.salary) as growth
from salaries a
join
(
    select emp_no,max(to_date) as cnt1
    from salaries
    group by emp_no
)b on a.emp_no=b.emp_no
where b.cnt1='9999-01-01' 
group by b.emp_no
order by growth


发表于 2025-03-07 20:21:23 回复(0)
Mysql
内向的华夫饼头像 内向的华夫饼
select a.emp_no as emp_no ,
       c.salary-b.salary as growth
    from(
        select emp_no,hire_date
        from employees
        where emp_no in (select emp_no from salaries where to_date = '9999-01-01')
    ) a
    left join salaries b
    on b.from_date = a.hire_date
    left join salaries c
    on c.to_date = '9999-01-01' and c.emp_no = a.emp_no
    order by growth;
发表于 2025-03-03 20:08:30 回复(0)
Mysql
涟漪的魔术师头像 涟漪的魔术师
select a.emp_no,tds-hds growth from
(select emp_no,salary tds from salaries
where to_date='9999-01-01') a
join (select s.emp_no,salary hds from salaries s
join employees e
on s.from_date=e.hire_date) b
on a.emp_no=b.emp_no
order by growth
直接找到入职工资和目前工资
发表于 2025-02-25 14:46:26 回复(0)
Mysql
AAAAA实习战神头像 AAAAA实习战神 
select e. emp_no, max(salary) - min(salary) growth
from employees e join salaries s on e.emp_no = s.emp_no
where e. emp_no in (select emp_no from salaries
group by emp_no
having max(to_date) = '9999-01-01')
group by e.emp_no
order by growth
为什么我这个不行呀，差一个。而且示例输入怎么看不了啊啊啊啊啊
发表于 2025-02-21 13:40:10 回复(1)
Mysql
熬夜_波比头像 熬夜_波比
select
e.emp_no,
max(salary)-min(salary) growth
from
employees e
join
(select
*
from
salaries
where emp_no in (select emp_no from salaries where to_date = '9999-01-01')) u
on e.emp_no = u.emp_no
where from_date  = hire_date or to_date = '9999-01-01'
group by emp_no
order by growth asc
发表于 2025-02-11 11:20:52 回复(0)
Mysql
Bulinbulin#113头像 Bulinbulin#113
开窗函数 
with t1 as (
    select
        emp_no,
        salary,
        lag (salary, 1, salary) over (partition by emp_no) as sub
    from
        salaries
    where
        emp_no in (
            select emp_no from salaries where to_date = '9999-01-01'
        )
    )
select 
    emp_no,
    sum(salary - sub) as growth
from t1
group by emp_no
order by growth asc



发表于 2025-01-09 15:04:55 回复(0)
Mysql
颠仔头像 颠仔 
select
    salaries.emp_no,
    t.salary - min(salaries.salary) growth
from salaries
inner join 
(
    select 
        emp_no,
        salary
    from salaries
    where to_date = '9999-01-01'
) t
on salaries.emp_no = t.emp_no
group by salaries.emp_no,t.salary
order by growth asc

发表于 2025-01-05 15:18:25 回复(0)
Mysql
胡乱自学头像 胡乱自学
with t1 as
(select emp_no,salary,to_date,
min(to_date) over (partition by emp_no) dt0,
max(to_date) over (partition by emp_no) dt1
from salaries)

select emp_no, max(salary)-min(salary) as growth from
(select emp_no, salary from t1 where to_date = dt0 and dt1 = '9999-01-01'
union all
select emp_no, salary from t1 where to_date = dt1 and dt1 = '9999-01-01') t2
group by emp_no
order by growth
不用第一张表，查最大最小to_date就行
发表于 2025-01-05 03:52:59 回复(0)
Mysql
牛客643275645号头像 牛客643275645号
SELECT b3.emp_no, MAX(b3.end_salary)-MAX(b3.start_salary) AS growth
FROM (
    SELECT E.emp_no, b2.salary, b2.from_date, b2.to_date,
    CASE WHEN b2.from_date=b2.minfrom_date THEN b2.salary ELSE 0 END AS start_salary,
    CASE WHEN b2.to_date=b2.maxto_date THEN b2.salary ELSE 0 END AS end_salary
    FROM employees E
    # salaries表格----筛选出在职员工信息,以及最初&最近一次的薪水
    JOIN (SELECT *
        FROM (
            SELECT emp_no, salary, from_date, to_date,
            MIN(from_date) OVER (PARTITION BY emp_no) AS minfrom_date,
            MAX(to_date) OVER (PARTITION BY emp_no) AS maxto_date
            FROM salaries
            ) AS b1
        WHERE b1.maxto_date='9999-01-01'
        AND (b1.from_date=b1.minfrom_date OR b1.to_date=b1.maxto_date)
        ) AS b2
    ON E.emp_no=b2.emp_no
) AS b3
GROUP BY b3.emp_no
ORDER BY growth
发表于 2024-12-27 16:57:08 回复(0)
Mysql
青衣白马少年时头像 青衣白马少年时 
select emp_no, max - min growth
from(select emp_no, salary max
from salaries
where to_date = '9999-01-01') a
join (select emp_no, min(salary) min
from salaries
group by emp_no) b using(emp_no)
order by growth

找最后的简单，但是找最初的我这里有点问题，有大佬可以给点建议吗
发表于 2024-12-26 23:11:11 回复(0)
Mysql
牛客876624653号头像 牛客876624653号
看了解题，感觉题有问题，都默认入职工资最低，最近一次统计一次工资最高，感觉不对
发表于 2024-12-03 13:58:14 回复(0)
Mysql
牛客456039645号头像 牛客456039645号
SELECT
    s1.emp_no AS emp_no,
    max( s2.salary - s1.salary ) AS growth 
FROM
    salaries AS s1
    LEFT JOIN salaries AS s2 ON s1.emp_no = s2.emp_no 
GROUP BY
    emp_no 
HAVING
    growth != 0;
那里出了问题啊，虽然时间复杂度是N²，但我看着没啥问题啊

发表于 2024-11-26 15:10:59 回复(0)
Mysql
kai0109头像 kai0109
select emp_no
,max(salary)-min(salary) growth
from salaries
where emp_no in (
    select emp_no
    from salaries
    where to_date = '9999-01-01')
group by emp_no
order by growth;

这样为什么不对呢？
发表于 2024-11-24 19:10:57 回复(0)
提交观点

问题信息

SQL
难度：
423条回答 46622浏览

热门推荐

通过挑战的用户

查看代码

相关试题

查找在职员工自入职以来的薪水涨幅情况

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号