给出各部门工资第二高的雇员名字、工资及所在部门

[编程题]给出各部门工资第二高的雇员名字、工资及所在部门

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

数据库

雇员表Employee

Id	Name	Salary	DepartmentId
1	Mandy	10000	1
2	Henry	21000	2
3	Tom	15000	2
4	Mike	20000	1

部门表Department

Id	Name
1	Sale A
2	Sale B

给出各部门工资第二高的雇员名字、工资及所在部门

ps: sqlite的in 语法中暂不支持>=2个字段同时in

算法知识视频讲解

潘多拉fshine

已修改：去除第一第二部门并列情况

select dept.Name, e.Name,e.Salary from Employee e,Department dept
    where e.DepartmentId = dept.Id and 
        e.Salary = (select max(distinct Salary) from Employee where DepartmentId = dept.Id 
        and Salary != (select max(Salary) from Employee where DepartmentId = dept.Id))

编辑于 2021-03-15 12:09:35 回复(1)

C/C++

nbgao

SELECT d.Name, e.Name, Salary
FROM Employee e, Department d
WHERE e.DepartmentId = d.Id
AND e.Salary < (
    SELECT max(Salary) FROM Employee e1 WHERE e1.DepartmentId = e.DepartmentId
)

发表于 2020-01-18 21:00:03 回复(2)

给你一份offer

select d.Name,e.Name,max(e.Salary) from Employee e ,Department d
where e.DepartmentId=d.Id
and
e.Salary<(select max(e.Salary)from Employee e group by e.DepartmentId)
group by e.DepartmentId;

发表于 2019-03-13 15:33:49 回复(0)

mumucgq

这个答案可以解决在有多条记录中，出现并列第二的问题

select d.Name,e.Name,e.Salary
from  Department d ,Employee  e
where e.id in(
	select a.Id
	from Employee a , Employee b
	where a.DepartmentId = b.DepartmentId
	and a.Id != b.Id
	and a.Salary < b.Salary
	group by a.Id
	having count(distinct b.Salary ) =1 )
and d.id = e.DepartmentId
order by d.Name asc, e.salary desc;

发表于 2021-03-12 20:46:25 回复(0)

旷视内推君

select d.Name,e.Name,Salary from Employee e ,Department d
where e.DepartmentId=d.Id and e.Salary<(
    select max(Salary) from Employee e1,Department d1 where e1.DepartmentId=d1.Id
    group by d1.Id
) group by d.Id

发表于 2020-04-13 10:11:16 回复(0)

努力写论文呀

select a.name, a.s, a.dep
from
    (select *, dense_rank() over(partition by tmp.dep order by tmp.s desc) t
    from
        (select e.Name as name, e.Salary as s, d.Name as dep
        from Employee as e
        left join Department as d
        on e.DepartmentId = d.Id) tmp) a
where a.t = 2;

对应输出应该为:

Sale A|Mandy|10000

你的输出为:

Mandy|10000|Sale A

简直无语，你们题目要求不是给出各部门工资第二高的雇员名字、工资及所在部门吗？？？？？？？？？

发表于 2019-07-19 11:58:42 回复(0)

do9core

要是能用Oracle就好了😐

SELECT t.emp_name,
       t.salary,
       t.dep_name,
  FROM (SELECT e.name emp_name,
               e.salary salary,
               d.name dep_name, 
               rank() over(order by e.salary) rank
          FROM employee e, department d
         WHERE e.departmentid = d.id(+)) t
 WHERE t.rank = 2;

发表于 2019-03-05 10:38:57 回复(1)