针对不同条件进行聚合
针对不同条件进行聚合
提问于 2020-01-15 16:07:26
我有一个窗口函数，如下所示：
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添加到这个查询中吗？
回答 1
Stack Overflow用户
发布于 2020-01-15 16:42:43
测试：
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 */页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59747248
复制相关文章
相似问题
腾讯云开发者
