oracle关于null与时间大小比较之后结果的郁闷问题。兄弟姐妹们帮忙呀。。

Question

create table SUBSCRIBTE_HIST
(
  IDSEQ            NUMBER(8) not null,
  RECORDSEQUENCEID VARCHAR2(18) not null,
  USERIDTYPE       NUMBER(4) not null,
  USERID           VARCHAR2(36) not null,
  SERVICETYPE      VARCHAR2(2) not null,
  SPID             VARCHAR2(21) not null,
  PRODUCTID        VARCHAR2(21) not null,
  UPDATETYPE       NUMBER(4) not null,
  UPDATETIME       DATE not null,
  UPDATEDESC       VARCHAR2(100),
  LINKID           VARCHAR2(20),
  CONTENT          VARCHAR2(140),
  EFFECTIVEDATE    DATE not null,
  EXPIREDATE       DATE not null,
  TIMESTAMP        VARCHAR2(10) not null,
  ENCODESTR        VARCHAR2(32),
  BATCHSTATUS      NUMBER(1) default 0 not null,
  CREATEDATE       DATE default sysdate not null
)
;insert into SUBSCRIBTE_HIST (IDSEQ, RECORDSEQUENCEID, USERIDTYPE, USERID, SERVICETYPE, SPID, PRODUCTID, UPDATETYPE, UPDATETIME, UPDATEDESC, LINKID, CONTENT, EFFECTIVEDATE, EXPIREDATE, TIMESTAMP, ENCODESTR, BATCHSTATUS, CREATEDATE)
values (10033, '1258705055531', 1, '13912345678', '1', '1001', '82', 2, to_date('14-11-2009 19:17:57', 'dd-mm-yyyy hh24:mi:ss'), '取消订购', null, 'qxc', to_date('14-11-2009 19:17:57', 'dd-mm-yyyy hh24:mi:ss'), to_date('14-11-2009 19:17:57', 'dd-mm-yyyy hh24:mi:ss'), '1120161735', null, 1, to_date('14-11-2009 19:17:57', 'dd-mm-yyyy hh24:mi:ss'));
insert into SUBSCRIBTE_HIST (IDSEQ, RECORDSEQUENCEID, USERIDTYPE, USERID, SERVICETYPE, SPID, PRODUCTID, UPDATETYPE, UPDATETIME, UPDATEDESC, LINKID, CONTENT, EFFECTIVEDATE, EXPIREDATE, TIMESTAMP, ENCODESTR, BATCHSTATUS, CREATEDATE)
values (10031, '1258704985937', 1, '13912345678', '1', '1001', '82', 1, to_date('14-11-2009 19:17:12', 'dd-mm-yyyy hh24:mi:ss'), '订购', null, 'dzc', to_date('14-11-2009 19:17:12', 'dd-mm-yyyy hh24:mi:ss'), to_date('14-11-2009 19:17:12', 'dd-mm-yyyy hh24:mi:ss'), '1120161625', null, 1, to_date('14-11-2009 19:17:12', 'dd-mm-yyyy hh24:mi:ss'));
insert into SUBSCRIBTE_HIST (IDSEQ, RECORDSEQUENCEID, USERIDTYPE, USERID, SERVICETYPE, SPID, PRODUCTID, UPDATETYPE, UPDATETIME, UPDATEDESC, LINKID, CONTENT, EFFECTIVEDATE, EXPIREDATE, TIMESTAMP, ENCODESTR, BATCHSTATUS, CREATEDATE)
values (10032, '1258705025343', 1, '13912345678', '1', '1001', '82', 2, to_date('14-11-2009 19:17:38', 'dd-mm-yyyy hh24:mi:ss'), '取消订购', null, 'qxc', to_date('14-11-2009 19:17:38', 'dd-mm-yyyy hh24:mi:ss'), to_date('14-11-2009 19:17:38', 'dd-mm-yyyy hh24:mi:ss'), '1120161705', null, 1, to_date('14-11-2009 19:17:38', 'dd-mm-yyyy hh24:mi:ss'));
commit;根据上面的表记录我做了一个查询
select distinct
decode(greatest(t1.ordertime,t2.canceltime),null,2,t1.ordertime,1,t2.canceltime,2) AS status
--nvl2(t2.canceltime,case when t1.ordertime>t2.canceltime then 1 else 2 end,1) as status 
from
(select userid,servicetype,spid,productid,updatetype,max(updatetime) ordertime
from subscribte_hist where updatetype=1 and batchstatus=1 group by userid,servicetype,spid,productid,updatetype
 )t1
full join
(
select userid,servicetype,spid,productid,updatetype,max(updatetime) canceltime
from subscribte_hist where updatetype=2 and batchstatus=1 group by userid,servicetype,spid,productid,updatetype
) t2
on (t1.userid=t2.userid and t1.spid=t2.spid and t1.productid=t2.productid);但结果非常的令人郁闷，因为按照正常情况我的两个t1.ordertime,t2.canceltime时间做比较，肯定是t2.canceltime大，然后decode里面应为2，但结果却为1,
当我用case when时，结果是正确的！所以我想问一下CSDN的高手们，这个问题是怎么解释的呢？希望大家帮忙看一下，谢谢！

Answer 1

SQL> select decode(greatest(sysdate,sysdate+1),null,2,sysdate,1,sysdate+1,2) as test from dual;
      TEST
----------
         2decode没问题啊

Answer 2

1、decode(greatest(t1.ordertime,t2.canceltime),null,2,t1.ordertime,1,t2.canceltime,2)2、nvl2(t2.canceltime,case when t1.ordertime>t2.canceltime then 1 else 2 end,1) 这两句的区别在于当t1.ordertime等于t2.canceltime时，
第一句返回的结果是1，第二句返回的结果是2。

Answer 3

并不是decode函数和case when有问题，是你在两者的逻辑上写的是有区别的，所以返回的结果也不尽相同。

Answer 4

我运行了一下你的sql，没发现你说的问题哦，两次的返回结果都是 2
[TEST@ora10gr1#2009-11-29/11:04:14] SQL>create table SUBSCRIBTE_HIST
  2  (
  3    IDSEQ            NUMBER(8) not null,
  4    RECORDSEQUENCEID VARCHAR2(18) not null,
  5    USERIDTYPE      NUMBER(4) not null,
  6    USERID          VARCHAR2(36) not null,
  7    SERVICETYPE      VARCHAR2(2) not null,
  8    SPID            VARCHAR2(21) not null,
  9    PRODUCTID        VARCHAR2(21) not null,
 10    UPDATETYPE      NUMBER(4) not null,
 11    UPDATETIME      DATE not null,
 12    UPDATEDESC      VARCHAR2(100),
 13    LINKID          VARCHAR2(20),
 14    CONTENT          VARCHAR2(140),
 15    EFFECTIVEDATE    DATE not null,
 16    EXPIREDATE      DATE not null,
 17    TIMESTAMP        VARCHAR2(10) not null,
 18    ENCODESTR        VARCHAR2(32),
 19    BATCHSTATUS      NUMBER(1) default 0 not null,
 20    CREATEDATE      DATE default sysdate not null
 21  )
 22  ;Table created.[TEST@ora10gr1#2009-11-29/11:04:15] SQL>
[TEST@ora10gr1#2009-11-29/11:04:15] SQL>insert into SUBSCRIBTE_HIST (IDSEQ, RECORDSEQUENCEID, USERIDTYPE, USERID, SERVICETYPE, SPID, PRODUCTID, UPDATETYPE, UPDATETIME, UPDATEDESC, LINKID, CONTENT, E
ECTIVEDATE, EXPIREDATE, TIMESTAMP, ENCODESTR, BATCHSTATUS, CREATEDATE)
  2  values (10033, '1258705055531', 1, '13912345678', '1', '1001', '82', 2, to_date('14-11-2009 19:17:57', 'dd-mm-yyyy hh24:mi:ss'), '取消订购', null, 'qxc', to_date('14-11-2009 19:17:57', 'dd-mm-y
y hh24:mi:ss'), to_date('14-11-2009 19:17:57', 'dd-mm-yyyy hh24:mi:ss'), '1120161735', null, 1, to_date('14-11-2009 19:17:57', 'dd-mm-yyyy hh24:mi:ss'));1 row created.[TEST@ora10gr1#2009-11-29/11:04:15] SQL>insert into SUBSCRIBTE_HIST (IDSEQ, RECORDSEQUENCEID, USERIDTYPE, USERID, SERVICETYPE, SPID, PRODUCTID, UPDATETYPE, UPDATETIME, UPDATEDESC, LINKID, CONTENT, E
ECTIVEDATE, EXPIREDATE, TIMESTAMP, ENCODESTR, BATCHSTATUS, CREATEDATE)
  2  values (10031, '1258704985937', 1, '13912345678', '1', '1001', '82', 1, to_date('14-11-2009 19:17:12', 'dd-mm-yyyy hh24:mi:ss'), '订购', null, 'dzc', to_date('14-11-2009 19:17:12', 'dd-mm-yyyy
24:mi:ss'), to_date('14-11-2009 19:17:12', 'dd-mm-yyyy hh24:mi:ss'), '1120161625', null, 1, to_date('14-11-2009 19:17:12', 'dd-mm-yyyy hh24:mi:ss'));1 row created.[TEST@ora10gr1#2009-11-29/11:04:16] SQL>insert into SUBSCRIBTE_HIST (IDSEQ, RECORDSEQUENCEID, USERIDTYPE, USERID, SERVICETYPE, SPID, PRODUCTID, UPDATETYPE, UPDATETIME, UPDATEDESC, LINKID, CONTENT, E
ECTIVEDATE, EXPIREDATE, TIMESTAMP, ENCODESTR, BATCHSTATUS, CREATEDATE)
  2  values (10032, '1258705025343', 1, '13912345678', '1', '1001', '82', 2, to_date('14-11-2009 19:17:38', 'dd-mm-yyyy hh24:mi:ss'), '取消订购', null, 'qxc', to_date('14-11-2009 19:17:38', 'dd-mm-y
y hh24:mi:ss'), to_date('14-11-2009 19:17:38', 'dd-mm-yyyy hh24:mi:ss'), '1120161705', null, 1, to_date('14-11-2009 19:17:38', 'dd-mm-yyyy hh24:mi:ss'));1 row created.[TEST@ora10gr1#2009-11-29/11:04:16] SQL>
[TEST@ora10gr1#2009-11-29/11:04:17] SQL>select distinct
  2  decode(greatest(t1.ordertime,t2.canceltime),null,2,t1.ordertime,1,t2.canceltime,2) AS status
  3  --nvl2(t2.canceltime,case when t1.ordertime>t2.canceltime then 1 else 2 end,1) as status
  4  from
  5  (select userid,servicetype,spid,productid,updatetype,max(updatetime) ordertime
  6  from subscribte_hist where updatetype=1 and batchstatus=1 group by userid,servicetype,spid,productid,updatetype
  7  )t1
  8  full join
  9  (
 10  select userid,servicetype,spid,productid,updatetype,max(updatetime) canceltime
 11  from subscribte_hist where updatetype=2 and batchstatus=1 group by userid,servicetype,spid,productid,updatetype
 12  ) t2
 13  on (t1.userid=t2.userid and t1.spid=t2.spid and t1.productid=t2.productid);    STATUS
----------
         2[TEST@ora10gr1#2009-11-29/11:04:26] SQL>select distinct
  2  --decode(greatest(t1.ordertime,t2.canceltime),null,2,t1.ordertime,1,t2.canceltime,2) AS status
  3  nvl2(t2.canceltime,case when t1.ordertime>t2.canceltime then 1 else 2 end,1) as status
  4  from
  5  (select userid,servicetype,spid,productid,updatetype,max(updatetime) ordertime
  6  from subscribte_hist where updatetype=1 and batchstatus=1 group by userid,servicetype,spid,productid,updatetype
  7  )t1
  8  full join
  9  (
 10  select userid,servicetype,spid,productid,updatetype,max(updatetime) canceltime
 11  from subscribte_hist where updatetype=2 and batchstatus=1 group by userid,servicetype,spid,productid,updatetype
 12  ) t2
 13  on (t1.userid=t2.userid and t1.spid=t2.spid and t1.productid=t2.productid);    STATUS
----------
         2[TEST@ora10gr1#2009-11-29/11:04:41] SQL>