各位帮小弟我看一上这样的功能能否实现-Oracle教程-爱易网页

各位帮小弟我看一上这样的功能能否实现

日期：2014-05-17　浏览次数：20696 次

各位帮我看一下这样的功能能否实现
表：
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 dual
UNION ALL 
SELECT 1,'B','0001' FROM dual
UNION ALL
SELECT 1,'C','0002' FROM dual
UNION ALL
SELECT 2,'A',null FROM dual
UNION ALL                       
SELECT 2,'B','0001' FROM dual
UNION ALL
SELECT 2,'C',NULL FROM dual
UNION ALL
SELECT 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;