select max(DECODE(dis_id,null,null,transaction_date)) from( select sysdate transaction_date,1 dis_id from dual union all select sysdate-2,null dis_id from dual)反馈结果是:27-11月-14不应该没有返回最大的日期呀
select max(DECODE(dis_id,null,null,transaction_date)) from( select sysdate-20 transaction_date,1 dis_id from dual union all select sysdate-2000, 2 dis_id from dual union all select sysdate-200, 3 dis_id from dual union all select sysdate-201, 12 dis_id from dual union all select sysdate-21, 20 dis_id from dual )就返回11-MAY-14,而不是最大的07-NOV-14
from(
select sysdate transaction_date,1 dis_id from dual
union all
select sysdate-2,null dis_id from dual)反馈结果是:27-11月-14不应该没有返回最大的日期呀
可以替换成nvl2(dis_id, transaction_date,null)
from(
select sysdate-20 transaction_date,1 dis_id from dual
union all
select sysdate-2000, 2 dis_id from dual
union all
select sysdate-200, 3 dis_id from dual
union all
select sysdate-201, 12 dis_id from dual
union all
select sysdate-21, 20 dis_id from dual
)就返回11-MAY-14,而不是最大的07-NOV-14
里面,c和d类型要一致,你这里c为null,被当成了字符串,于是d发生了隐式转换:to_char(transaction_date)
对于字符串来说,11-MAY-14要比07-NOV-14大