sql - Postgresql group by多行

我有一个名为hr_holidays_by_calendar的表。我只想过滤掉同一个员工在同一天有两个假期的行。
表：

我试过的问题：
根本不可能解决这个问题。

select hol1.employee_id, hol1.leave_date, hol1.no_of_days, hol1.leave_state
from hr_holidays_by_calendar hol1
inner join
    (select employee_id, leave_date
    from hr_holidays_by_calendar hol1
    group by employee_id, leave_date
    having count(*)>1)sub
on hol1.employee_id=sub.employee_id and hol1.leave_date=sub.leave_date
where hol1.leave_state != 'refuse'
order by hol1.employee_id, hol1.leave_date

最佳答案

返回存在重复的所有行：

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 this
   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?