当前位置: 代码迷 >> 综合 >> 牛客网SQL编程(一):sql12获取所有部门薪水最高的emp_no,dept_no,salary
  详细解决方案

牛客网SQL编程(一):sql12获取所有部门薪水最高的emp_no,dept_no,salary

热度:52   发布时间:2023-12-07 02:31:28.0

题目:获取所有部门中当前员工薪水最高的相关信息,给出dept_no,emp_no以及其对应的salary,当前表示to_date=‘9999-01-01’
隐含要求:按照部门升序排列

错误示范(虽然能通过测试,但实际上是错的)

SELECT d.dept_no, d.emp_no, s.salary
FROM dept_emp as d
INNER JOIN salaries as s
ON d.emp_no=s.emp_no
WHERE d.to_date='9999-01-01’ AND s.to_date='9999-01-01'
GROUP BY d.dept_no
HAVING salary=MAX(s.salary);

错误点1: d.emp_no是非聚合字段,不能出现在SELECT。因为一个聚合字段(dept_no)对应多个非聚合字段(emp_no),所以选择的时候,会随机选择非聚合字段中的任何一个,于是出错。
错误点2: 根据错误点1,如果强行写上述代码,当多人同时拥有最高薪水时也无法查出。
错误点3: HAVING语句用来与聚合函数联合使用,过滤GROUP BY语句返回的记录集,筛选的是分组,例如HAVING AVG(score)>=80,筛选的是那些平均成绩大于80的分组,而上面的HAVING salary=MAX(s.salary)实际上是想筛选分组中的记录。这是错误的。

补充:
GROUP BY语句与HAVING语句的使用:参考资料:https://www.cnblogs.com/geogre123/p/11177204.html
语法:

SELECT column_1, column_2, … column_n, aggregate_function(expression), constant
FROM tables
WHERE predicates
GROUP BY column_1, column_2, … column_n
HAVING condition_1 … condition_n;

**注意:**因为聚合函数通过作用一组值而只返回一个单一值,因此,在SELECT语句中出现的字段要么为一个聚合函数的输入值,如COUNT(course),要么为GROUP BY语句中指定的字段,要么是常数,否则会出错。
简而言之:使用GROUP BY子句时,SELECT子句中只能有聚合键、聚合函数、常数。

答案解析:
1、如果题目是获取每个部门的最高工资的dept_no,salary

select dept_no,max(salary)
from dept_emp d,salaries s
where d.emp_no = s.emp_no
and d.to_date='9999-01-01'
and s.to_date='9999-01-01'
group by dept_no;

2、本题目要求的是所有部门的最高工资对应的emp_no,dept_no,salary

**正确答案: 先创建一张表:记录部门和该部门最高工资;然后将三张表连接;最后当员工的工资正好等于部门最大工资时选择。
**

select r.dept_no,ss.emp_no,r.maxSalary from (
select d.dept_no,max(s.salary)as maxSalary from dept_emp d,salaries s
where d.emp_no=s.emp_no
and d.to_date=‘9999-01-01’
and s.to_date=‘9999-01-01’
group by d.dept_no
)as r,salaries ss,dept_emp dd
where r.maxSalary=ss.salary
and r.dept_no=dd.dept_no
and dd.emp_no=ss.emp_no
and ss.to_date=‘9999-01-01’
and dd.to_date=‘9999-01-01’
order by r.dept_no asc