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这张表,得到相同结果
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这张表,得到相同结果
解决方案 »
- 请教Oracle开卷考试题..关于row_number() over
- 怎么样自动添加数据?????????
- 如何将一个返回值作为一个字段名来使用?
- oracle 7中的数据导出后能导到oracle 8中吗?
- oracle启动时sqlplus用不了,必须执行connect internale ;startupp等命令
- oracle中这个查询怎么写???在线等,急
- 登陆不上DBA studio的问题!
- TNSLSNR.exe应用程序错误(散高分,在线等待,急!急!急!)
- 我有正版oracle815,谁买?
- ORA-12170 TNS 连接超时 (同一网段用plsql无法连接,不同网段可以)
- oracle数据库查询
- oracle9i 拆分:字母加数字的字符串
SS_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去得到你想要的结果
是个不错的方法,可以试试