表:
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语句怎样实现
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语句怎样实现
select id,bsg,ftn
from t1
order by id,bsg
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
)
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;