SELECT ckd.FFCKD_ID,
COUNT (DISTINCT (a.XCRZ_ID)) XCCSZS,
COUNT (DISTINCT (x.XCRZ_ID)) XCCSDY,
COUNT (DISTINCT (y.XCRZ_ID)) XCCSFDY
FROM SS_FFCKD ckd,
(SELECT FFCKD_ID,XCRZ_ID
FROM TEST111
WHERE TO_CHAR (Xc_Sj, 'YYYYMMDD') BETWEEN 20160101 AND 2016013) a
(SELECT FFCKD_ID,XCRZ_ID
FROM TEST111
WHERE TO_CHAR (Xc_Sj, 'YYYYMMDD') BETWEEN 20160101 AND 2016013
AND TO_CHAR (create_time, 'YYYYMMDD') BETWEEN 20160101 AND 20160130) x,
(SELECT FFCKD_ID,XCRZ_ID
FROM TEST111
WHERE TO_CHAR (Xc_Sj, 'YYYYMMDD') BETWEEN 20160101 AND 2016013
AND (TO_CHAR (create_time, 'YYYYMMDD') < 20160101 OR TO_CHAR (create_time, 'YYYYMMDD') > 20160130)) y
WHERE TO_CHAR (ckd.CREATE_TIME, 'YYYYMMDD') < 20160101
AND ckd.FFCKD_ID = a.FFCKD_ID(+)
AND ckd.FFCKD_ID = x.FFCKD_ID(+)
AND ckd.FFCKD_ID = y.FFCKD_ID(+)
AND ckd.XCFS_DM = 1
GROUP BY FFCKD_ID
需求：
只掃描一次TEST111 表
不需要掃描查詢3次TEST111這張表，得到相同結果
uj5u.com熱心網友回復：
利用case when去改寫sqluj5u.com熱心網友回復：
select bz,count(distinct XCRZ_ID) from (select ckd.FFCKD_ID,XCRZ_ID,case when TO_CHAR (create_time, 'YYYYMMDD') BETWEEN 20160101 AND 20160130 then 0 esle 1 end bz fromSS_FFCKD ckd,
TEST111 a
where
ckd.FFCKD_ID = a.FFCKD_ID(+) and
TO_CHAR (Xc_Sj, 'YYYYMMDD') BETWEEN 20160101 AND 2016013 AND
ckd.XCFS_DM = 1) group by ckd.FFCKD_ID,bz;
bz= 0 是代表 TO_CHAR (create_time, 'YYYYMMDD') BETWEEN 20160101 AND 20160130;
bz= 1 是代表 TO_CHAR (create_time, 'YYYYMMDD') < 20160101 OR TO_CHAR (create_time, 'YYYYMMDD') > 20160130
然后在通過with as去得到你想要的結果
uj5u.com熱心網友回復：
用WITH AS是個不錯的方法，可以試試
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/113587.html
標籤：高級技術
上一篇：如何使用基于contiki作業系統的ipv6開發套件將mx231cc傳感器上采集的資料經過usbstick傳送到電腦資料庫中？