SQL-从子查询中筛选常见记录

Question

我有一个表，存储有关员工的数据&他们各自的部门。

一名员工可以属于>1个部门。

下面不详细介绍，而是我从多个表的联接中查询的结果。

查询：

Select 
e.EmpID,
d.Department,
ed.Date,
ed.Action
from Employee e
inner join Emp_Dept ed on ....
inner join Department d on ....
where .....

提取的数据：

日期01-01-2021>添加01-01-2021[/tr>已删除添加2021年2月1日04-01-2021已删除

EmpID	部门	行动
1	食品
2	食品	添加
2	食品	2021年4月1日
2	自动	2021年1月1日
3	电气	添加
3	电气	已删除
3	自动	04-01-2021

如果我理解正确，你可以使用not exists:

select t.*
from t
where t.action = 'ADDED' and
not exists (select 1
from t t2
where t2.empid = t.empid and
t2.department = t.department and
t2.action = 'REMOVED'
);

编辑：

如果你希望某个部门的员工添加更多，然后删除：

select empid, deptid
from t
group by empid deptid
having sum(case when action = 'ADDED' then 1
when action = 'REMOVED' then -1
end);

使用分析LISTAGG函数获取每个员工和部门的操作的完整历史

listagg(ACTION,',') within group (order by DATE_D, ACTION) over (partition by EMPID, DEPARTMENT) ACTION_LST

注意顺序是在日期和ACTION上定义的，就好像你在同一天被删除了，你可能想看到ADDED，removed

基本查询(使用行集作为tab(

with dt as (
select EMPID, DEPARTMENT, DATE_D, ACTION,
listagg(ACTION,',') within group (order by DATE_D, ACTION) over (partition by EMPID, DEPARTMENT) ACTION_LST
from tab
EMPID, DEPARTMENT, DATE_D,          ACTION, ACTION_LST
1   Food        01.01.2021 00:00:00 ADDED   ADDED
2   Auto        01.01.2021 00:00:00 ADDED   ADDED
2   Food        01.01.2021 00:00:00 ADDED   ADDED,REMOVED
2   Food        04.01.2021 00:00:00 REMOVED ADDED,REMOVED
3   Auto        04.01.2021 00:00:00 REMOVED REMOVED
3   Electric    02.01.2021 00:00:00 ADDED   ADDED,REMOVED
3   Electric    04.01.2021 00:00:00 REMOVED ADDED,REMOVED

无论如何，这种方法都是为了查看所有不同的操作组合。

现在，您只需添加一个过滤查询，即可消除不需要的组合：

with dt as (
select EMPID, DEPARTMENT, DATE_D, ACTION,
listagg(ACTION,',') within group (order by DATE_D, ACTION) over (partition by EMPID, DEPARTMENT) ACTION_LST
from tab)
select EMPID, DEPARTMENT, DATE_D, ACTION
from dt
where ACTION_LST not in ('ADDED,REMOVED')

使用聚合并在HAVING子句中设置条件：

select e.EmpID, d.Department
from Employee e
inner join Emp_Dept ed on ....
inner join Department d on ....
where .....
group by e.EmpID, d.Department
having count(distinct case when ed.Action in ('ADDED', 'REMOVED') THEN ed.Action END) < 2

您可以使用12C及以上版本的模式识别功能(更多详细信息请参阅《数据仓库指南》(，它还可以处理添加和删除序列，在这里看起来很自然：

select *
from t
match_recognize(
partition by empid, department
order by dt
/*Find matched rows*/
measures
classifier() as cls
/*To include all the rows and then filter out classified rows*/
all rows per match with unmatched rows
/*Addition followed by removal*/
pattern (add0 rem0)
define
add0 as action = 'ADDED',
rem0 as action = 'REMOVED'
)
/*Exclude matched (added -> removed)*/
where cls is null

EMPID|DEPARTMENT|DT|CLS|ACTION----：|：--------|：---------|：---|：------1 |食品|01-JAN-21 |空|添加2 |自动|01-JAN-21 |空|添加3 |自动|01-APR-21 |空|已删除

/*Create a sequence of add -> remove -> add*/
insert into t
select 2, 'Food', date '2021-05-01', 'ADDED' from dual union all
select 2, 'Food', date '2021-06-01', 'REMOVED' from dual union all
select 2, 'Food', date '2021-07-01', 'ADDED' from dual

select *
from t
match_recognize(
partition by empid, department
order by dt
measures
classifier() as cls
all rows per match with unmatched rows
pattern (add0 rem0)
define
add0 as action = 'ADDED',
rem0 as action = 'REMOVED'
)
where cls is null

EMPID|DEPARTMENT|DT|CLS|ACTION----：|：--------|：---------|：---|：------1 |食品|01-JAN-21 |空|添加2 |自动|01-JAN-21 |空|添加2 |食品|JUL-21 |空|添加3 |自动|01-APR-21 |空|已删除

db<gt；小提琴这里