SELECT A.E_DATE, SUM(DECODE(A.E_SOURCE,'门诊',1,0)) 门诊人数, SUM(CASE WHEN A.E_SOURCE='门诊' AND B.I_NAME='项目A' THEN 1 ELSE 0 END), SUM(CASE WHEN A.E_SOURCE='门诊' AND B.I_NAME='项目B' THEN 1 ELSE 0 END), SUM(CASE WHEN A.E_SOURCE='门诊' AND C.IS_ABNORMAL=1 THEN 1 ELSE 0 END), SUM(DECODE(A.E_SOURCE,'住院',1,0)) 门诊人数, SUM(CASE WHEN A.E_SOURCE='住院' AND B.I_NAME='项目A' THEN 1 ELSE 0 END), SUM(CASE WHEN A.E_SOURCE='住院' AND B.I_NAME='项目B' THEN 1 ELSE 0 END), SUM(CASE WHEN A.E_SOURCE='住院' AND C.IS_ABNORMAL=1 THEN 1 ELSE 0 END), FROM EXAMS A, EXAM_ITEM B, REPORTS C WHERE A.E_NO = B.E_NO(+) AND A.E_NO = C.E_NO(+) GROUP BY A.E_DATE ORDER BY A.E_DATE 这个试一下
select a.E_DATE 检查时间, (select count(1) from EXAMS where E_SOURCE='门诊' and E_DATE=a.E_DATE) 门诊人数, SUM(DECODE(a.E_SOURCE,'门诊',DECODE(b.I_NAME,'项目A',1,0),0)) 门诊(项目A)人数, SUM(DECODE(a.E_SOURCE,'门诊',DECODE(b.I_NAME,'项目B',1,0),0)) 门诊(项目B)人数, SUM(DECODE(a.E_SOURCE,'门诊',DECODE(c.IS_ABNORMAL,'1',1,0),0)) 门诊阳性例数, (select count(1) from EXAMS where E_SOURCE='住院' and E_DATE=a.E_DATE) 住院人数, SUM(DECODE(a.E_SOURCE,'住院',DECODE(b.I_NAME,'项目A',1,0),0)) 住院(项目A)人数, SUM(DECODE(a.E_SOURCE,'住院',DECODE(b.I_NAME,'项目B',1,0),0)) 住院(项目B)人数, SUM(DECODE(a.E_SOURCE,'住院',DECODE(c.IS_ABNORMAL,'1',1,0),0)) 住院阳性例数 FROM EXAMS a,EXAM_ITEM b,REPORTS c WHERE a.E_NO = b.E_NO(+) AND a.E_NO = c.E_NO(+) GROUP BY a.E_DATE ORDER BY a.E_DATE 这样应该可以!!!
SUM(DECODE(A.E_SOURCE,'门诊',1,0)) 门诊人数,
SUM(CASE WHEN A.E_SOURCE='门诊' AND B.I_NAME='项目A' THEN 1 ELSE 0 END),
SUM(CASE WHEN A.E_SOURCE='门诊' AND B.I_NAME='项目B' THEN 1 ELSE 0 END),
SUM(CASE WHEN A.E_SOURCE='门诊' AND C.IS_ABNORMAL=1 THEN 1 ELSE 0 END),
SUM(DECODE(A.E_SOURCE,'住院',1,0)) 门诊人数,
SUM(CASE WHEN A.E_SOURCE='住院' AND B.I_NAME='项目A' THEN 1 ELSE 0 END),
SUM(CASE WHEN A.E_SOURCE='住院' AND B.I_NAME='项目B' THEN 1 ELSE 0 END),
SUM(CASE WHEN A.E_SOURCE='住院' AND C.IS_ABNORMAL=1 THEN 1 ELSE 0 END),
FROM EXAMS A, EXAM_ITEM B, REPORTS C
WHERE A.E_NO = B.E_NO(+)
AND A.E_NO = C.E_NO(+)
GROUP BY A.E_DATE
ORDER BY A.E_DATE
这个试一下
如果你要统计不重复,那么你需要判定一个优先级 如果检查了项目A就把检查的项目B去掉,C表同样处理。
(select count(1) from EXAMS where E_SOURCE='门诊' and E_DATE=a.E_DATE) 门诊人数,
SUM(DECODE(a.E_SOURCE,'门诊',DECODE(b.I_NAME,'项目A',1,0),0)) 门诊(项目A)人数,
SUM(DECODE(a.E_SOURCE,'门诊',DECODE(b.I_NAME,'项目B',1,0),0)) 门诊(项目B)人数,
SUM(DECODE(a.E_SOURCE,'门诊',DECODE(c.IS_ABNORMAL,'1',1,0),0)) 门诊阳性例数,
(select count(1) from EXAMS where E_SOURCE='住院' and E_DATE=a.E_DATE) 住院人数,
SUM(DECODE(a.E_SOURCE,'住院',DECODE(b.I_NAME,'项目A',1,0),0)) 住院(项目A)人数,
SUM(DECODE(a.E_SOURCE,'住院',DECODE(b.I_NAME,'项目B',1,0),0)) 住院(项目B)人数,
SUM(DECODE(a.E_SOURCE,'住院',DECODE(c.IS_ABNORMAL,'1',1,0),0)) 住院阳性例数
FROM EXAMS a,EXAM_ITEM b,REPORTS c
WHERE a.E_NO = b.E_NO(+)
AND a.E_NO = c.E_NO(+)
GROUP BY a.E_DATE
ORDER BY a.E_DATE 这样应该可以!!!