我这有一条sql语句,请高手来优化!!奖励分只是其次,主要是想看看能否有高手可以解决这个问题~
探索求知 2013-05-16 05:45:47 select * From ccps_traderecord td left join ccps_creditinfo ci on td.tr_no=ci.ci_tr_no where 1=1 and (
ci.CI_SHA256 in (select distinct ci.CI_SHA256 from ccps_traderecord t left join ccps_creditinfo ci on t.tr_no=ci.ci_tr_no where 1=1
and to_date('2013-05-09 17:31:55','yyyy-MM-dd hh24:mi:ss') <= t.TR_DATETIME
and to_date('2013-05-16 17:31:55','yyyy-MM-dd hh24:mi:ss') >= t.TR_DATETIME and ci.CI_SHA256 is not null)
and ci.CI_EMAIL in (select distinct ci.CI_EMAIL from ccps_traderecord t left join ccps_creditinfo ci on t.tr_no=ci.ci_tr_no where 1=1
and to_date('2013-05-09 17:31:55','yyyy-MM-dd hh24:mi:ss') <= t.TR_DATETIME
and to_date('2013-05-16 17:31:55','yyyy-MM-dd hh24:mi:ss') >= t.TR_DATETIME and ci.CI_EMAIL is not null)
or ci.CI_IPADDRESS in (select distinct ci.CI_IPADDRESS from ccps_traderecord t left join ccps_creditinfo ci on t.tr_no=ci.ci_tr_no where 1=1
and to_date('2013-05-09 17:31:55','yyyy-MM-dd hh24:mi:ss') <= t.TR_DATETIME
and to_date('2013-05-16 17:31:55','yyyy-MM-dd hh24:mi:ss') >= t.TR_DATETIME)
or ci.CI_ADDRESS in (select distinct ci.CI_ADDRESS from ccps_traderecord t left join ccps_creditinfo ci on t.tr_no=ci.ci_tr_no where 1=1
and to_date('2013-05-09 17:31:55','yyyy-MM-dd hh24:mi:ss') <= t.TR_DATETIME
and to_date('2013-05-16 17:31:55','yyyy-MM-dd hh24:mi:ss') >= t.TR_DATETIME)
) and (
ci.ci_firstname||' '||ci.ci_lastname not in (select distinct ci.ci_firstname||' '||ci.ci_lastname from ccps_traderecord t left join
ccps_creditinfo ci on t.tr_no=ci.ci_tr_no where 1=1 and to_date('2013-05-09 17:31:55','yyyy-MM-dd hh24:mi:ss') <= t.TR_DATETIME
and to_date('2013-05-16 17:31:55','yyyy-MM-dd hh24:mi:ss') >= t.TR_DATETIME and ci.ci_firstname||' '||ci.ci_lastname is not null)
and ci.CI_TEL not in (select distinct ci.CI_TEL from ccps_traderecord t left join ccps_creditinfo ci on t.tr_no=ci.ci_tr_no where 1=1
and to_date('2013-05-09 17:31:55','yyyy-MM-dd hh24:mi:ss') <= t.TR_DATETIME
and to_date('2013-05-16 17:31:55','yyyy-MM-dd hh24:mi:ss') >= t.TR_DATETIME and ci.CI_TEL is not null)
or ci.CI_ZIPCODE not in (select distinct ci.CI_ZIPCODE from ccps_traderecord t left join ccps_creditinfo ci on t.tr_no=ci.ci_tr_no where
1=1 and to_date('2013-05-09 17:31:55','yyyy-MM-dd hh24:mi:ss') <= t.TR_DATETIME
and to_date('2013-05-16 17:31:55','yyyy-MM-dd hh24:mi:ss') >= t.TR_DATETIME)
) order by td.TR_DATETIME desc