如何从分析查询中排除选择分区？

Question

下面是我的场景：

对于ID=1，cheese_year_seqno of 201111，它的一个行有一个XX的供应商代码，所以我想排除所有201111 seqno，但是可以对201222行进行排序。如果给定的XX中没有供应商year_seqno，那么就可以使用所有行进行排序。

由于ID=2没有XX的供应商代码，所以它的所有行都可以进行排序。

with cheese_row as
(
select 1 as cheese_id, '201111' as cheese_year_seqno, 2 as cheese_lot, 1 as cheese_batch, 'AA' as cheese_vendor,trunc(sysdate-356) as cheese_batch_date from dual union all
select 1 as cheese_id, '201111' as cheese_year_seqno, 2 as cheese_lot, 2 as cheese_batch, 'BB' as cheese_vendor,trunc(sysdate-356) as cheese_batch_date from dual union all
select 1 as cheese_id, '201111' as cheese_year_seqno, 2 as cheese_lot, 3 as cheese_batch, 'XX' as cheese_vendor,trunc(sysdate-350) as cheese_batch_date from dual union all
select 1 as cheese_id, '201222' as cheese_year_seqno, 1 as cheese_lot, 1 as cheese_batch, 'AA' as cheese_vendor,trunc(sysdate-856) as cheese_batch_date from dual union all
select 1 as cheese_id, '201222' as cheese_year_seqno, 1 as cheese_lot, 2 as cheese_batch, 'DD' as cheese_vendor,trunc(sysdate-830) as cheese_batch_date from dual union all
select 2 as cheese_id, '201333' as cheese_year_seqno, 2 as cheese_lot, 3 as cheese_batch, 'CC' as cheese_vendor,trunc(sysdate-300) as cheese_batch_date from dual union all
select 2 as cheese_id, '201333' as cheese_year_seqno, 1 as cheese_lot, 1 as cheese_batch, 'AA' as cheese_vendor,trunc(sysdate-301) as cheese_batch_date from dual union all
select 2 as cheese_id, '201444' as cheese_year_seqno, 1 as cheese_lot, 1 as cheese_batch, 'DD' as cheese_vendor,trunc(sysdate-290) as cheese_batch_date from dual

)
select cheese_id,
       cheese_year_seqno,
       cheese_lot,
       cheese_batch,
       cheese_vendor,
       cheese_batch_date,
       rank() over (partition by cheese_id
                        order by cheese_batch_date desc,
                                 cheese_batch desc,
                                 cheese_lot desc) as ch_rank1
    from cheese_row

/* If a cheese_year_seqno has  cheese_vendor = XX then exclude the whole
    cheese_year_seqno, but return all other batch seqno.
    Rank the remaining cheese_year_seqno rows.
    In this case the 20111 year_seqno has an XX as a cheese_vendor, 
    therefore return and rank only the two rows with 201222 year_seqno.     
*/    

预期结果：

Return 
 ID   SEQNO    LOT  BA   VEN   DATE        RNK1
---- -------- ---- ---- ----- ----------- ------
 1    201222   1    2    DD    17-JUN-12   1
 1    201222   1    2    AA    22-MAY-12   2
 2    201444   1    1    DD    09-DEC-13   1
 2    201333   2    3    CC    29-NOV-13   2
 2    201333   1    1    AA    28-NOV-13   3

Answer 1

使用第二个解析函数来确定年份是否合格，然后对此进行筛选：

with cheese_row as(
      select 1 as cheese_id, '201111' as cheese_year_seqno, 2 as cheese_lot, 1 as cheese_batch, 'AA' as cheese_vendor,trunc(sysdate-356) as cheese_batch_date from dual union all
      select 1 as cheese_id, '201111' as cheese_year_seqno, 2 as cheese_lot, 2 as cheese_batch, 'BB' as cheese_vendor,trunc(sysdate-356) as cheese_batch_date from dual union all
      select 1 as cheese_id, '201111' as cheese_year_seqno, 2 as cheese_lot, 3 as cheese_batch, 'XX' as cheese_vendor,trunc(sysdate-350) as cheese_batch_date from dual union all
      select 1 as cheese_id, '201222' as cheese_year_seqno, 1 as cheese_lot, 1 as cheese_batch, 'AA' as cheese_vendor,trunc(sysdate-856) as cheese_batch_date from dual union all
      select 1 as cheese_id, '201222' as cheese_year_seqno, 1 as cheese_lot, 2 as cheese_batch, 'DD' as cheese_vendor,trunc(sysdate-830) as cheese_batch_date from dual union all
      select 2 as cheese_id, '201333' as cheese_year_seqno, 2 as cheese_lot, 3 as cheese_batch, 'CC' as cheese_vendor,trunc(sysdate-300) as cheese_batch_date from dual union all
      select 2 as cheese_id, '201333' as cheese_year_seqno, 1 as cheese_lot, 1 as cheese_batch, 'AA' as cheese_vendor,trunc(sysdate-301) as cheese_batch_date from dual union all
      select 2 as cheese_id, '201444' as cheese_year_seqno, 1 as cheese_lot, 1 as cheese_batch, 'DD' as cheese_vendor,trunc(sysdate-290) as cheese_batch_date from dual
    )
select cheese_id, cheese_year_seqno, cheese_lot, cheese_batch, cheese_vendor, cheese_batch_date,
       rank() over (partition by cheese_id
                        order by cheese_batch_date desc,
                                 cheese_batch desc,
                                 cheese_lot desc) as ch_rank1
from (select cr.*,
             sum(case when cheese_vendor = 'XXX' then 1 else 0 end) over (partition by cheese_year_seqno) as XXXFlag
      from cheese_row
     ) cr
where XXXFlag = 0;

Answer 2

添加where子句，如下所示：

where cheese_year_seqno NOT IN (
  select cheese_year_seqno from cheese_row where cheese_vendor = 'XX'
  )