SQL实战练习题(5)-求每个部门最高工资的名单-leetcode sql 184

目录

• 问题
• 答案

问题

答案

create table Employee(
	id int primary key auto_increment,
	name varchar(32),
	salary int ,
	departmentId int
)charset=utf8mb4;

create table department(
	id int primary key auto_increment,
	name varchar(32)
)charset=utf8mb4;

insert into Employee(name, salary, departmentId) values('john', 90000, 1),('jobs', 80000, 2),('white', 60000, 2),('smith', 90000, 1);
insert into department(name) values('it'), ('sales');

---

SELECT d.name as dname, e.name as ename, e.salary 
FROM Employee e join department d on e.departmentId = d.id
WHERE e.salary = (
	SELECT MAX(salary) 
	FROM Employee e2 
	WHERE e2.departmentId = d.id
);

SELECT dep.name as dname, emp1 .name as ename, emp1.salary 
FROM Employee emp1, department dep,(select e2.departmentId, MAX(salary) maxSalary from Employee e2 group by e2.departmentId) as emp2
WHERE emp1.departmentId = dep .id 
	and emp1.departmentId = emp2.departmentId
	and emp1 .salary = emp2.maxSalary   
;

 -- [求任意第N高工资的人，相似问题参考](https://www.cnblogs.com/UsingStuding/diary/2022/01/11/15790396.html)？
drop PROCEDURE if EXISTS MyProcGetNthSalary;
create PROCEDURE MyProcGetNthSalary(i int)
begin
		SELECT d.name as dname, emp.name as ename, emp.salary  
		FROM Employee emp  join department d on emp.departmentId = d.id 
		WHERE i = (
			select count(distinct e2.salary) as serialNum
			from Employee e2
			where emp.salary < e2.salary  and emp .departmentId = e2.departmentId 
		)
		ORDER BY d.name
	;
END

call MyProcGetNSalary(0);   -- 0表示最高，1表示次高的(第二高)