根据下一行和id选择数据

Question

I have the following table and data,
TABLE - DEAL
ID  DEALID
1   DEAL1
2   DEAL2
ALL DEAL2
3   DEAL2
4   DEAL5
5   DEAL5
ALL DEAL6   

我只想得到下面的数据

ID  DEALID
1   DEAL1
4   DEAL5
5   DEAL5
ALL DEAL6

我想根据id列和dealid列的值选择数据。如果id的值为'ALL'并且对应的dealid重复，则省略所有带有该dealid的记录

Answer 1

根据您发布的示例数据，看看这是否有帮助。阅读代码中的注释

SQL> with
2  test (id, dealid) as
3    -- sample data
4    (select '1'  , 'deal1' from dual union all
5     select '2'  , 'deal2' from dual union all
6     select 'ALL', 'deal2' from dual union all
7     select '3'  , 'deal2' from dual union all
8     select '4'  , 'deal5' from dual union all
9     select '5'  , 'deal5' from dual union all
10     select 'ALL', 'deal6' from dual
11    ),
12  all2 as
13    -- DEALIDs that contain different ID values, and MAX of them is ALL
14    (select dealid
15     from test
16     group by dealid
17     having min(id) <> max(id)
18        and max(id) = 'ALL'
19    )
20  select t.id, t.dealid
21  from test t join all2 a on a.dealid <> t.dealid;
ID  DEALI
--- -----
1   deal1
4   deal5
5   deal5
ALL deal6
SQL>