Postgresql分组为多行 [英] Postgresql group by for multiple lines

问题描述

我将此表命名为 hr_holidays_by_calendar .我只想过滤出同一名员工在同一天有两张假的行.

I have this table named hr_holidays_by_calendar. I just want to filter out the rows where the same employee is having two leaves in same day.

表 hr_holidays_by_calendar :

我尝试过的查询:
解决这个问题的地方不远.

Query I tried:
Wasn't anywhere near in solving this.

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

推荐答案

这将返回 存在重复项的所有行 :

This returns all rows where a duplicate exists:

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<>拒绝" 应该适用.您将必须定义需求.我的示例完全排除了具有 leave_state ='refuse'(以及 leave_state IS NULL 带有它！)的行.

It's unclear where leave_state <> 'refuse' should apply. You would have to define requirements. My example excludes rows with leave_state = 'refuse' (and leave_state IS NULL with it!) completely.

ctid 是您未公开(未定义?)主键的穷人替代品.

ctid is a poor man's surrogate for your undisclosed (undefined?) primary key.

相关:

• 我如何(或者我可以)在多个列上选择DISTINCT吗?
• 在EXISTS子查询中更容易阅读的是什么?

这篇关于Postgresql分组为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！