表：
id bsg ftn
1 A 0001
1 B 0001
1 C 0002
2 A
2 B 0001
2 C
2 D 0001
要求是这样的 在id相同的情况下 并且 ftn值相同的情况下 就要看bsg字段值的优先级 A>B>C>D 那么最后得到的结果为：
id bsg ftn
1 A 0001
1 B
1 C 0002
2 A
2 B 0001
2 C
2 D
请问这用SQL语句怎样实现
------解决方案--------------------
- SQL code
WITH t AS(SELECT 1 id,'A'bsg,'0001'ftn FROM dualUNION ALL SELECT 1,'B','0001' FROM dualUNION ALLSELECT 1,'C','0002' FROM dualUNION ALLSELECT 2,'A',null FROM dualUNION ALL SELECT 2,'B','0001' FROM dualUNION ALLSELECT 2,'C',NULL FROM dualUNION ALLSELECT 2,'D','0001' FROM dual)SELECT id,bsg,Decode(rn,1,ftn,null)ftn FROM( select id,bsg,ftn,Row_Number() over (PARTITION BY id,ftn ORDER BY bsg)rn from t order by id,bsg)
------解决方案--------------------
with temp1 as (select id,min(bsg) bsg,ftn from t1 group by id,ftnz)
select t1.id,t1.bsg,
case when
exists (select 1 from temp1 where temp1.id=t1.id and temp1.bsg=t1.bsg) then t1.ftn
else '' end ftn
from t1;