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 descoracle,优化
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 descoracle,优化
ci.ci_firstname||' '||ci.ci_lastname not in (select distinct ci.ci_firstname||' '||ci.ci_lastname
1,直接判断不可以吗?还拼接后再折腾?2,不用IN 用 exist, 就可以不用distinct了,而且更快更省