题解 | #获取每个部门中当前员工薪水最高的相关信息#

with tmp as (select d.emp_no,salary,dept_no from dept_emp d left join salaries s on d.emp_no=s.emp_no) select a.dept_no,b.emp_no,a.maxSalary from (select dept_no,max(salary) maxSalary from tmp group by dept_no) a left join tmp b on a.dept_no=b.dept_no and b.salary=a.maxsalary order by a.dept_no asc;

1.拉宽表 2.取条件(记得group by) 3.宽表 left join 条件 4.出结果

with b as (select d.emp_no,salary,dept_no from dept_emp d left join salaries s on d.emp_no=s.emp_no), a as(select dept_no,max(salary) maxSalary from b group by dept_no) select a.dept_no,b.emp_no,a.maxSalary from a left join b on a.dept_no=b.dept_no and b.salary=a.maxsalary order by a.dept_no asc;

全部评论
SELECT d.dept_no, d.emp_no, s.salary FROM dept_emp d INNER JOIN salaries s ON d.emp_no=s.emp_no WHERE s.salary in ( SELECT MAX(s2.salary) FROM dept_emp d2 INNER JOIN salaries s2 ON d2.emp_no=s2.emp_no AND d2.dept_no = d.dept_no ) ORDER BY d.dept_no;
点赞 回复 分享
发布于 2022-06-15 14:26
SELECT d1.dept_no, d1.emp_no, s1.salary FROM dept_emp as d1 INNER JOIN salaries as s1 ON d1.emp_no=s1.emp_no AND d1.to_date='9999-01-01' AND s1.to_date='9999-01-01' WHERE s1.salary in (SELECT MAX(s2.salary) FROM dept_emp as d2 INNER JOIN salaries as s2 ON d2.emp_no=s2.emp_no AND d2.to_date='9999-01-01' AND s2.to_date='9999-01-01' AND d2.dept_no = d1.dept_no ) ORDER BY d1.dept_no;
点赞 回复 分享
发布于 2022-06-15 14:19

相关推荐

头顶尖尖的程序员:我也是面了三四次才放平心态的。准备好自我介绍,不一定要背熟,可以记事本写下来读。全程控制语速,所有问题都先思考几秒,不要急着答,不要打断面试官说话。
点赞 评论 收藏
分享
07-10 14:08
已编辑
江西农业大学 Java
拒绝无效加班的小学生...:期望3k吗?java这辈子有了
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务