sql - Postgresql 嵌套聚合函数

Question

我想找出当月请假次数最多的员工。

我从这个查询开始:

select MAX(TotalLeaves) as HighestLeaves 
FROM (SELECT emp_id, count(adate) as TotalLeaves 
      from attendance 
      group by emp_id) AS HIGHEST;

但我在显示员工 ID 和获取当月的结果时遇到了问题。请帮帮我。

Answer 1

如果您只想在当前查询中显示相应的 employee_id，您可以对结果进行排序并获得前 1 行，并且您需要在组之前过滤数据以仅获得当前月份:

select
    emp_id, TotalLeaves
from (
    select emp_id, count(adate) as TotalLeaves 
    from attendance
    where adate >= date_trunc('month', current_date)
    group by emp_id
) as highest
order by TotalLeaves desc
limit 1;

实际上，你根本不需要在这里使用子查询:

select emp_id, count(adate) as TotalLeaves 
from attendance
where adate >= date_trunc('month', current_date)
group by emp_id
order by TotalLeaves desc
limit 1;

sql fiddle demo