题目:获取每个部门中当前员工薪水最高的相关信息
注意了,这道题目,分组函数只能查出来:每个部门的最高薪水,group by dept_no ,根据部门分组,绝对不能group by dept_no,emp_no,不能根据(部门、员工编号)联合分组:
(select e1.dept_no, max(s1.salary) as salary
from
dept_emp e1
join
salaries s1
on
e1.emp_no = s1.emp_no
group by e1.dept_no ) s2
方法一: 三张表联查
select
e.dept_no , e.emp_no , s.salary as maxSalary
from
dept_emp e
join
salaries s
on
e.emp_no = s.emp_no
join
(select e1.dept_no, max(s1.salary) as salary
from
dept_emp e1
join
salaries s1
on
e1.emp_no = s1.emp_no
group by e1.dept_no ) s2 #这是s2表
on
s2.dept_no = e.dept_no and s2.salary = s.salary
where
e.to_date='9999-01-01' and s.to_date='9999-01-01'
order by
e.dept_no;
优化:通过使用 WITH 语句创建了一个名为 max_salaries 的临时表,这样可以更清晰地理解查询的逻辑。
WITH max_salaries AS (
SELECT e1.dept_no, MAX(s1.salary) AS max_salary
FROM salaries s1
INNER JOIN dept_emp e1 ON e1.emp_no = s1.emp_no
GROUP BY e1.dept_no
)
SELECT
e.dept_no,
e.emp_no,
s.salary AS max_salary
FROM
dept_emp e
JOIN
salaries s ON e.emp_no = s.emp_no
JOIN
max_salaries ms ON e.dept_no = ms.dept_no AND s.salary = ms.max_salary
ORDER BY
e.dept_no;
方法二:居然还能这样玩!!! in 的用法可以使用元组形式
select d.dept_no,d.emp_no,s.salary
from dept_emp as d
join salaries as s
on d.emp_no = s.emp_no
where (d.dept_no,salary) in (
# in 的用法可以使用元组形式 :找出部门和对应的最高工资
select d.dept_no,max(s.salary) salary
from dept_emp as d
join salaries as s
on d.emp_no = s.emp_no
group by d.dept_no
)
order by d.dept_no
在 MySQL 中,子查询中使用 IN
操作符时,需要确保子查询返回的结果列数与外部查询中的比较列数相匹配。在这个查询中,子查询返回三列 (dept_no, max_salary, from_date)
,而外部查询试图将其与两列 (dept_no, salary)
进行比较,这导致了错误:
为了解决这个问题,可以将子查询的结果限制为两列,即 (dept_no, max_salary)
,并且在外部查询中使用这两列进行比较。以下是修改后的查询:
这样就能够正确地比较子查询和外部查询的列数,并得到想要的结果。