Oracle如何选择记录的优先级

Question

有一张表里记录了地址信息(stid)、光资源设备类型(factype)、光资源接入方式(cnway)的信息，但是由于一个地址可以关联多个光资源，所以同一个stid可能对应多个设备类型、多种接入方式。现在要另外生成一张表，是要一个地址对应一种光资源设备，规则如下：如果地址关联了factype=3的，则只取3这种类型的接入方式，不管是否还关联有其他设备类型；如果地址没关联3设备类型，但关联了factype=9的设备类型，则取9这种类型的接入方式，不管是否还关联有其他设备类型；最后一种情况是设备只关联了factype=8的，就取8这种类型的接入方式。
select addr,stid,count(stid) 
from tmp_epon_serarea 
group by addr,stid 
having count(stid) >1
用这个可以查询出tmp_epon_serarea表里一个地址关联多个光资源的情况。上面的问题该怎么完成呢？

Answer 1

你的意思是按照factype进行分类插入？如果，factype分类比较多呢

Answer 2

那你可以建一个优先级的表出来，下面一个简单的例子
with tbl as
(
    select '001' as addr, '001' as stid, 9 as factype from dual
     union all
    select '001' as addr, '001' as stid, 3 as factype from dual
     union all
    select '001' as addr, '001' as stid, 8 as factype from dual
     union all
    select '002' as addr, '002' as stid, 8 as factype from dual
     union all
    select '002' as addr, '002' as stid, 9 as factype from dual
     union all
    select '003' as addr, '003' as stid, 8 as factype from dual
),
factype_sort as
(
    select 3 as factype, 1 as line from dual
     union all
    select 9 as factype, 2 as line from dual
     union all
    select 8 as factype, 3 as line from dual
)
select t.addr, t.stid, t.factype
  from (select a.*, row_number() over(partition by addr, stid order by b.line) as line
          from tbl a left join factype_sort b
            on a.factype = b.factype) t
 where t.line = 1;

Answer 3

select t.addr,t.stid,t.factype,t.cnway
FROM tmp_epon_serarea t 
WHERE EXISTS(SELECT 1 from tmp_epon_serarea x
    WHERE x.stid=t.stid AND x.addr=t.addr
    group by x.addr,x.stid  
    having count(x.stid) >1)
AND t.factype=3 --如果地址关联了factype=3的，则只取3这种类型的接入方式，不管是否还关联有其他设备类型；
UNION ALL
select t.addr,t.stid,t.factype,t.cnway
FROM tmp_epon_serarea t 
WHERE EXISTS(SELECT 1 from tmp_epon_serarea x
    WHERE x.stid=t.stid AND x.addr=t.addr
    group by x.addr,x.stid  
    having count(x.stid) >1)
AND NOT EXISTS(SELECT 1 from tmp_epon_serarea x --如果地址没关联3设备类型，
    WHERE x.stid=t.stid AND x.addr=t.addr 
    AND t.factype=3
    group by x.addr,x.stid  
    having count(x.stid) >1)    
AND t.factype=9 --但关联了factype=9的设备类型，则取9这种类型的接入方式，不管是否还关联有其他设备类型；
UNION ALL
select t.addr,t.stid,t.factype,t.cnway
FROM tmp_epon_serarea t 
WHERE EXISTS(SELECT 1 from tmp_epon_serarea x
    WHERE x.stid=t.stid AND x.addr=t.addr
    group by x.addr,x.stid  
    having count(x.stid) >1)
AND NOT EXISTS(SELECT 1 from tmp_epon_serarea x --只关联了factype=8的,3和9的要排除掉
    WHERE x.stid=t.stid AND x.addr=t.addr 
    AND (t.factype=3 OR t.factype=9)
    group by x.addr,x.stid  
    having count(x.stid) >1)    
AND t.factype=8 --只关联了factype=8的

Answer 4

select t.addr,t.stid,t.factype,t.cnway
FROM tmp_epon_serarea t  
WHERE EXISTS(SELECT 1 from tmp_epon_serarea x
  WHERE x.stid=t.stid AND x.addr=t.addr
  group by x.addr,x.stid   
  having count(x.stid) >1)
AND NOT EXISTS(SELECT 1 from tmp_epon_serarea x --如果地址没关联3设备类型，
  WHERE x.stid=t.stid AND x.addr=t.addr  
  AND t.factype=3
  group by x.addr,x.stid   
  having count(x.stid) >1)   
AND t.factype=9 --但关联了factype=9的设备类型，则取9这种类型的接入方式，不管是否还关联有其他设备类型；
这个还是不对啊，并没有把地址没关联3设备类型，但关联了9设备类型这种筛选出来；还是把所有关联了9设备类型（包括同时关联了3设备类型）的全选出来了