OR145. 给出各部门工资第二高的雇员名字、工资及所在部门
描述
| 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 |
Sqlite 解法, 执行用时: 11ms, 内存消耗: 3320KB, 提交时间: 2021-03-13
select d.Name,e.Name,e.Salary
from Employee e,Department d
where e.DepartmentId = d.Id
and e.Salary < (
select max(a.Salary)
from Employee a
group by a.DepartmentId
) ;
Sqlite 解法, 执行用时: 11ms, 内存消耗: 3320KB, 提交时间: 2020-10-31
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
)