SELECT * from employee
INSERT into employee VALUES(
'1', 'Joe', '85000', '1'),
( 2 , 'Henry' , 80000 , 2),
( 3 , 'Sam' , 60000 , 2),
( 4 , 'Max' , 90000 , 1),
( 5 , 'Janet' , 69000 , 1),
( 6 , 'Randy' , 85000 , 1),
( 7 , 'Will' , 70000 , 1
)
INSERT into department VALUES ('1', 'IT'), ( 2 , 'Sales' )
---解题答案：
解释: 在IT部门:
- Max的工资最高
- 兰迪和乔都赚取第二高的独特的薪水
- 威尔的薪水是第三高的
在销售部:
- 亨利的工资最高
- 山姆的薪水第二高
- 没有第三高的工资，因为只有两名员工
select d.Name as Department,e.Name as Employee,e.Salary as Salary from Employee as e left join Department as d on e.DepartmentId = d.Id where e.Id in ( select e1.Id from Employee as e1 left join Employee as e2 on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary group by e1.Id having count(distinct e2.Salary) <= 2 ) and e.DepartmentId in (select Id from Department) order by d.Id asc,e.Salary desc
