sql - Postgresql group by 多行
问题描述
我有这张表名为hr_holidays_by_calendar
. 我只想过滤掉同一员工在同一天有两次休假的行。
表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 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
是您未公开(未定义?)主键的穷人的替代品。
有关的:
推荐阅读
- r - 如何绘制第二个系数(不包括绘图中的截距)我可以选择使用 abline 绘制哪个系数?
- php - PHP:如何将数组作为函数参数传播
- laravel - 如何在带有关系表的帖子中显示评论的用户名
- cors - Why it has been blocked by CORS policy: Response to preflight request doesn't pass access control check: It does not have HTTP ok status.?
- python - 在 python 中打开或创建文件并附加到它
- typescript - 为什么 Typescript 不会为遗漏的参数引发错误?
- python - From where should we hit third party apis in django?
- r - 使用 map_df 和 read_excel 在 excel 工作簿中导入和重命名多个工作表
- python - Python : Why is: not None = True
- c++11 - share std::vector using shared memory IPC in linux