SELECT CKD FFCKD_ID,
COUNT (DISTINCT (a.X CRZ_ID) XCCSZS,
COUNT (DISTINCT (7.0.x.x CRZ_ID) XCCSDY,
COUNT (DISTINCT (y.X CRZ_ID) XCCSFDY
The FROM SS_FFCKD CKD,
(SELECT FFCKD_ID, XCRZ_ID
The FROM TEST111
WHERE TO_CHAR (Xc_Sj, 'YYYYMMDD') BETWEEN 20160101 AND 2016013) in a
(SELECT FFCKD_ID, XCRZ_ID
The 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
The FROM TEST111
WHERE TO_CHAR (Xc_Sj, 'YYYYMMDD') BETWEEN 20160101 AND 2016013
AND (TO_CHAR (create_time, 'YYYYMMDD') & lt; 20160101 the OR TO_CHAR (create_time, 'YYYYMMDD') & gt; 20160130))
yWHERE TO_CHAR (CKD CREATE_TIME, 'YYYYMMDD') & lt; 20160101
AND chronic kidney disease (CKD). FFCKD_ID=a.F FCKD_ID (+)
AND chronic kidney disease (CKD). FFCKD_ID=x.F FCKD_ID (+)
AND chronic kidney disease (CKD). FFCKD_ID=y.F FCKD_ID (+)
AND chronic kidney disease (CKD). The XCFS_DM=1
GROUP BY FFCKD_ID
Requirements:
Scan only once TEST111 table
Don't need to scan the query three TEST111 this form, get the same results

CodePudding user response:

Use case the when to rewrite the SQL

CodePudding user response:

The 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 from
SS_FFCKD CKD,
TEST111 a
Where
Chronic kidney disease (CKD). FFCKD_ID=a.F FCKD_ID (+) and
TO_CHAR (Xc_Sj, 'YYYYMMDD') BETWEEN 20160101 AND 2016013 AND
Chronic kidney disease (CKD). XCFS_DM=1) group by CKD. FFCKD_ID, bz.

Bz=0 represents TO_CHAR (create_time, 'YYYYMMDD') BETWEEN 20160101 AND 20160130;
Bz=1 represents TO_CHAR (create_time, 'YYYYMMDD') & lt; 20160101 the OR TO_CHAR (create_time, 'YYYYMMDD') & gt; 20160130

And then through the with the as to get the results you want

CodePudding user response:

Use WITH the AS
It is a good method, can have a try