不使用任何子查询按部门获取最高薪资

Question

假设我们有两个数据库表- emp和dept，它们由以下列组成

emp：empid，deptid，工资

dept：deptid，deptname

emp中的deptid列可以与dept列中的deptid列连接。请注意，有些部门没有员工。对于这些情况，dept表中的deptid将不存在于emp表中。我们需要找到每个部门的最高工资。对于没有员工的部门，我们需要从emp表中为他们分配最高的工资。一个要求是不能使用子查询，但允许使用CTE (公共表表达式)。

下面是我构建的查询：

with cte as 
(Select d.deptid, e.salary, row_number() over (partition by d.deptid order by e.salary desc) as rnk,
row_number() over(order by e.salary desc) as salary_rank    
from emp e 
join dept d on e.deptid = dept.deptid),

top_salary as 
(Select d.deptid, e.salary 
from emp e 
join dept d on e.deptid = dept.deptid
order by e.salary desc
limit 1)


(Select d.deptid, cte.salary 
from cte 
join dept d on d.deptid = cte.deptid
where cte.rnk = 1) as t1

UNION 

(Select d.deptid, ts.salary  
from dept d 
left join cte on cte.deptid = d.deptid 
left join top_salary ts on ts.deptid = cte.deptid 
where cte.salary is null
)

但我不确定我是否做得对，尤其是在部门没有员工的情况下。我也不确定我围绕UNION子句编写的2个查询是否被视为子查询。如果它们确实是子查询，那么我是否可以不用任何子查询重写该查询？

Answer 1

我们需要在每个部门找到最高的薪水。对于没有员工的部门，我们需要从emp表中为他们分配最高的工资。

你的尝试似乎过于复杂：

with edmax as (
      select e.deptid, max(e.salary) as max_salary
      from emp
      group by e.deptid
     ),
     emax as (
      select max(e.salary) as max_salary
     )
select d.*, max(edmax.max_salary, emax.max_salary) as max_salary
from dept d left join
     edmax
     on d.deptid = edmax.deptid cross join
     emax;

其基本思想是计算每个部门的最高工资，然后“默认”为总体最高工资。

顺便说一句，只有通过联接才能做到这一点：

select d.deptid, d.name,
       coalesce(max(de.salary), max(d.salary))
from emp e cross join
     dept d left join
     dept de
     on de.deptid = e.deptid
group by d.deptid, d.name;

我不推荐这种方法，但您可能想了解它。