针对不同条件进行聚合

Question

我有一个窗口功能如下：

select d.Name as Department, a. Name as Employee, a.sum(salary) 
from ( select e.*, dense_rank() over (partition by DepartmentId order by Salary desc) as DeptPayRank 
from Employee e 
) a 
join Department d
on a. DepartmentId = d. Id 
where DeptPayRank =1 
group by 1,2

如果我还想拿所有工资的最小值，我可以添加到此查询中DepPayRank = 3，或者不可能？

Answer 1

测试：

SELECT d.Name AS Department, 
a.Name AS Employee, 
SUM(CASE WHEN DeptPayRank = 1 THEN salary END),
MIN(CASE WHEN DeptPayRank = 3 THEN salary END)
FROM ( SELECT e.*, 
DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS DeptPayRank 
from Employee e 
) a 
JOIN Department d ON a.DepartmentId = d.Id 
GROUP BY 1,2
/* HAVING COUNT(CASE WHEN DeptPayRank = 1 THEN 1 END) > 0 */