条件是在一个检索结果集中,同一个mNo里,如果语言有中文的话,就选择中文的数据,否则如果有法语的话就用法语,都没有的话就用英语这个SQL文是个结果集的列子:
select
A.mNo,
B.lang,
C.context
from
A,B,C结果
mNo lang context
--------------------
001 chi aaaa
001 fra bbbb
001 eng cccc
002 fra dddd
002 eng eeee
003 enu ffff希望得到的结果
mNo lang context
--------------------
001 chi aaaa
002 fra ddd
003 enu ffff
select
A.mNo,
B.lang,
C.context
from
A,B,C结果
mNo lang context
--------------------
001 chi aaaa
001 fra bbbb
001 eng cccc
002 fra dddd
002 eng eeee
003 enu ffff希望得到的结果
mNo lang context
--------------------
001 chi aaaa
002 fra ddd
003 enu ffff
with tb as(
select '001' mNo,'chi' lang,'aaaa' context from dual union all
select '001', 'fra', 'bbbb' from dual union all
select '001', 'eng', 'cccc' from dual union all
select '002', 'fra', 'dddd' from dual union all
select '002', 'eng', 'eeee' from dual union all
select '003', 'enu', 'ffff' from dual)
select mNo,lang,context
from(select mNo,
lang,
context,
row_number() over(partition by mNo order by decode(lang,'chi',1,'fra',2,'eng',3,null) nulls last) rn
from tb)
where rn=1
with tb as(
select '001' mNo,'chi' lang,'aaaa' context from dual union all
select '001', 'fra', 'bbbb' from dual union all
select '001', 'eng', 'cccc' from dual union all
select '002', 'fra', 'dddd' from dual union all
select '002', 'eng', 'eeee' from dual union all
select '003', 'enu', 'ffff' from dual)
select mNo,lang,context
from(select mNo,
lang,
context,
row_number() over(partition by mNo order by decode(lang,'chi',1,'fra',2,'eng',3,4)) rn
from tb)
where rn=1MNO LAN CONT
--- --- ----
001 chi aaaa
002 fra dddd
003 enu ffff
--------------------
001 chi aaaa
001 chi test
001 fra bbbb
001 eng cccc
002 fra dddd
002 eng eeee
003 enu ffff现在的结果
MNO LAN CONT
--- --- ----
001 chi aaaa
002 fra dddd
003 enu ffff希望结果
MNO LAN CONT
--- --- ----
001 chi aaaa
001 chi test
002 fra dddd
003 enu ffff
row_number() over(partition by mNo,context order by decode(lang,'chi',1,'fra',2,'eng',3,4)) rn
row_number() over(partition by mNo order by decode(lang,'chi',1,'fra',2,'eng',3,4)) rn--改成
dense_rank() over(partition by mNo order by decode(lang,'chi',1,'fra',2,'eng',3,4)) rn
并且加distinct行不行?