表格式:
Col1 col2
A 11
B 33
* 22 目的:当col1 为 A 时 得出COL2相应的值11,当col1 为 B 时 得出COL2相应的值33,当col2为 D 时 得出col2的值为 22,表中无 D 相关的数据,因此取通用的*的值22
Col1 col2
A 11
B 33
* 22 目的:当col1 为 A 时 得出COL2相应的值11,当col1 为 B 时 得出COL2相应的值33,当col2为 D 时 得出col2的值为 22,表中无 D 相关的数据,因此取通用的*的值22
from table_name
where col1='D' or (col1='*' and not exists(
select 1 from table_name b where b.col1='D'
));
假如
col1 col11 ... 好多列都属于这种情况,就是如果存在则选择存在的,如果不存在就选择 * 的。那岂不是要嵌套很多SQL?
from table_name
where col1 in('D','E','F'.....) or (col1='*' and not exists(
select 1 from table_name b where b.col1 in ('D','E','F'.....)
));
union all select 'B',33 from dual
union all select '*',22 from dual)
select * from(
select * from tt
where (col1='D' or col1='*')
order by 1 desc)
where rownum=1
union all select 'B',33 from dual
union all select '*',22 from dual)
select * from(
select tt.*,row_number()over(order by decode(col1,'D',1,'*',2,3))rn from tt)
where rn=1
from table_name
where col1='D' or (col1='*' and not exists(
select 1 from table_name b where b.col1='D'
));select nvl(select col2 from table_name where col1='D', select col2 from table_name where col1='*') from dual