多行Postgresql group by

存在重复项的所有行

SELECT employee_id, leave_date, no_of_days, leave_state
FROM   hr_holidays_by_calendar h
WHERE  EXISTS (
   SELECT -- select list can be empty for EXISTS
   FROM   hr_holidays_by_calendar
   WHERE  employee_id = h.employee_id
   AND    leave_date = h.leave_date
   AND    leave_state <> 'refuse'
   AND    ctid <> h.ctid
   )
AND    leave_state <> 'refuse'
ORDER  BY employee_id, leave_date;

不清楚在哪里 leave_state <> 'refuse' leave_state = 'refuse' (和 leave_state IS NULL 用它!)完全。

ctid 是一个穷人的代替品为你的未公开(未定义?)主键。

• How do I (or can I) SELECT DISTINCT on multiple columns?
• What is easier to read in EXISTS subqueries?

NOT EXISTS :

select h1.employee_id, h1.leave_date, h1.no_of_days, h1.leave_state
from hr_holidays_by_calendar h1
where 
  h1.leave_state <> 'refuse'
  and not exists (
    select 1
    from hr_holidays_by_calendar h2
    where 
      h1.employee_id = h2.employee_id
      and h1.leave_date = h2.leave_date
      group by employee_id, leave_date
      having count(*) > 1
  )

这将丢弃每一个(雇员,日期)对,其中他们有一个以上的行(在同一天离开)。

我没有考虑天数,因为这似乎是错误的-你不能在同一天休假两次,持续不同的天数。如果应用程序允许,请考虑应用其他逻辑。另外,您不应该让这些记录首先进入表中:-)

我相信 GROUP BY

select hol1.employee_id, hol1.leave_date, max(hol1.no_of_days)
from hr_holidays_by_calendar hol1
where hol1.leave_state != 'refuse'
group by hol1.employee_id, hol1.leave_date

现在还不清楚,如果两行有不同的 no_of_days

如果需要完整的行,有一种方法使用窗口函数:

select hc.*
from (select hc.*, count(*) over (partition by employee_id, leave_date) as cnt
      from hr_holidays_by_calendar hc
     ) hc
where cnt >= 2;

如果您只需要员工id和日期,那么聚合是合适的。