现在有三个表,他们的是一对多的关系检查表:EXAMS
E_NO | P_NAME | E_DATE | E_SOURCE
001 | 病人A | 2012-02-27 | 门诊
002 | 病人B | 2012-02-28 | 住院
003 | 病人C | 2012-02-28 | 门诊检查项目表:EXAM_ITEM
I_ID | E_NO | I_NAME | I_COST
01 | 001 | 项目A | 12
02 | 001 | 项目B | 13
03 | 002 | 项目A | 12
04 | 003 | 项目A | 12报告表:REPORTS
R_ID | E_NO | R_CONTENT | IS_ABNORMAL
1 | 001 | abcdefgh | 1
2 | 002 | jkljklji | 0
3 | 002 | kjijesa | 0
4 | 003 | jkjkljlie | 1现在的要求是要用SQL语句查出一下格式的表:检查时间
门诊人数
门诊(项目A)人数
门诊(项目B)人数
门诊阳性例数
住院人数
住院(项目A)人数
住院(项目B)人数
住院阳性例数select EXAMS.E_DATE 检查时间,
SUM(DECODE(EXAMS.E_SOURCE,'门诊',1,0)) 门诊人数,
SUM(DECODE(EXAMS.E_SOURCE,'门诊',DECODE(EXAM_ITEM.I_NAME,'项目A',1,0),0)) 门诊(项目A)人数,
SUM(DECODE(EXAMS.E_SOURCE,'门诊',DECODE(EXAM_ITEM.I_NAME,'项目B',1,0),0)) 门诊(项目B)人数,
SUM(DECODE(EXAMS.E_SOURCE,'门诊',DECODE(REPORTS.IS_ABNORMAL,'1',1,0),0)) 门诊阳性例数,
SUM(DECODE(EXAMS.E_SOURCE,'住院',1,0)) 住院人数,
SUM(DECODE(EXAMS.E_SOURCE,'住院',DECODE(EXAM_ITEM.I_NAME,'项目A',1,0),0)) 住院(项目A)人数,
SUM(DECODE(EXAMS.E_SOURCE,'住院',DECODE(EXAM_ITEM.I_NAME,'项目B',1,0),0)) 住院(项目B)人数,
SUM(DECODE(EXAMS.E_SOURCE,'住院',DECODE(REPORTS.IS_ABNORMAL,'1',1,0),0)) 住院阳性例数
FROM EXAMS,EXAM_ITEM,REPORTS
WHERE EXAMS.E_NO = EXAM_ITEM.E_NO(+)
AND EXAMS.E_NO = REPORTS.E_NO(+)
GROUP BY EXAMS.E_DATE
ORDER BY EXAMS.E_DATE
请教这个语句怎么写?我自己写的语句统计数据不准确,要怎么样才能让这样的数据统计出来呢?
E_NO | P_NAME | E_DATE | E_SOURCE
001 | 病人A | 2012-02-27 | 门诊
002 | 病人B | 2012-02-28 | 住院
003 | 病人C | 2012-02-28 | 门诊检查项目表:EXAM_ITEM
I_ID | E_NO | I_NAME | I_COST
01 | 001 | 项目A | 12
02 | 001 | 项目B | 13
03 | 002 | 项目A | 12
04 | 003 | 项目A | 12报告表:REPORTS
R_ID | E_NO | R_CONTENT | IS_ABNORMAL
1 | 001 | abcdefgh | 1
2 | 002 | jkljklji | 0
3 | 002 | kjijesa | 0
4 | 003 | jkjkljlie | 1现在的要求是要用SQL语句查出一下格式的表:检查时间
门诊人数
门诊(项目A)人数
门诊(项目B)人数
门诊阳性例数
住院人数
住院(项目A)人数
住院(项目B)人数
住院阳性例数select EXAMS.E_DATE 检查时间,
SUM(DECODE(EXAMS.E_SOURCE,'门诊',1,0)) 门诊人数,
SUM(DECODE(EXAMS.E_SOURCE,'门诊',DECODE(EXAM_ITEM.I_NAME,'项目A',1,0),0)) 门诊(项目A)人数,
SUM(DECODE(EXAMS.E_SOURCE,'门诊',DECODE(EXAM_ITEM.I_NAME,'项目B',1,0),0)) 门诊(项目B)人数,
SUM(DECODE(EXAMS.E_SOURCE,'门诊',DECODE(REPORTS.IS_ABNORMAL,'1',1,0),0)) 门诊阳性例数,
SUM(DECODE(EXAMS.E_SOURCE,'住院',1,0)) 住院人数,
SUM(DECODE(EXAMS.E_SOURCE,'住院',DECODE(EXAM_ITEM.I_NAME,'项目A',1,0),0)) 住院(项目A)人数,
SUM(DECODE(EXAMS.E_SOURCE,'住院',DECODE(EXAM_ITEM.I_NAME,'项目B',1,0),0)) 住院(项目B)人数,
SUM(DECODE(EXAMS.E_SOURCE,'住院',DECODE(REPORTS.IS_ABNORMAL,'1',1,0),0)) 住院阳性例数
FROM EXAMS,EXAM_ITEM,REPORTS
WHERE EXAMS.E_NO = EXAM_ITEM.E_NO(+)
AND EXAMS.E_NO = REPORTS.E_NO(+)
GROUP BY EXAMS.E_DATE
ORDER BY EXAMS.E_DATE
请教这个语句怎么写?我自己写的语句统计数据不准确,要怎么样才能让这样的数据统计出来呢?
解决方案 »
- 【技术求助】关于C/S程序登录用户的安全性问题
- ORACLE9.2.7,用D:\oracle\oradata目录下的文件,如何恢复数据库?请高手指点
- 请教分析函数
- ORACLE 与SQL2000 连接
- oracle数据库中的表因事务处理挂起无法查看,如何处理?
- 实体化视图!!!!!
- 救命呀!!!initialization or shutdown inprogress 错误如何解决 ,各位大虾帮帮忙把
- 关于init.ora中内存分配的问题,送高分!
- 是否有专业PL/SQL编辑器(能够提供类似VC的类成员或方法提示、格式自动排版等功能)可以下载 ??
- 如何根据分隔符一行转多行?
- 在sqlplus中选出了数据库中的数据,可以把这些数据写入某个excel中某个特定的位置吗
- 请问高手,如何才能将连接池中反复count,造成内存递增的影响消除掉呢?
检查项目表:EXAM_ITEM
报告表:REPORTS
这两张表的主键没有任何意义,要这样设计,数据库就没有主键存在的必要了。这样有多表查询时效率极低。
2、
SQL没看出来有问题,应该是对的
SUM(DECODE(EXAMS.E_SOURCE,'门诊',1,0)) 门诊人数,
SUM(DECODE(EXAMS.E_SOURCE,'门诊',DECODE(EXAM_ITEM.I_NAME,'项目A',1,0),0)) "门诊(项目A)人数",
SUM(DECODE(EXAMS.E_SOURCE,'门诊',DECODE(EXAM_ITEM.I_NAME,'项目B',1,0),0)) "门诊(项目B)人数",
SUM(DECODE(EXAMS.E_SOURCE,'门诊',DECODE(REPORTS.IS_ABNORMAL,'1',1,0),0)) 门诊阳性例数,
SUM(DECODE(EXAMS.E_SOURCE,'住院',1,0)) 住院人数,
SUM(DECODE(EXAMS.E_SOURCE,'住院',DECODE(EXAM_ITEM.I_NAME,'项目A',1,0),0)) "住院(项目A)人数",
SUM(DECODE(EXAMS.E_SOURCE,'住院',DECODE(EXAM_ITEM.I_NAME,'项目B',1,0),0)) "住院(项目B)人数",
SUM(DECODE(EXAMS.E_SOURCE,'住院',DECODE(REPORTS.IS_ABNORMAL,'1',1,0),0)) 住院阳性例数
FROM EXAMS,EXAM_ITEM,REPORTS
WHERE EXAMS.E_NO = EXAM_ITEM.E_NO(+)
AND EXAMS.E_NO = REPORTS.E_NO(+)
GROUP BY EXAMS.E_DATE
ORDER BY EXAMS.E_DATE或者把全角的括号删了
这些问题不解决,楼主的问题永远无法搞定。
未经测试,效率不高,就复制速度一下,如下:
select EXAMS.E_DATE 检查时间,
(select count(1) from EXAM_ITEM item where EXAMS.E_SOURCE = '门诊' AND EXAMS.E_NO = item.E_NO AND item.I_NAME = '项目A') 门诊(项目A)人数,
(select count(1) from EXAM_ITEM item where EXAMS.E_SOURCE = '门诊' AND EXAMS.E_NO = item.E_NO AND item.I_NAME = '项目B') 门诊(项目B)人数,
(select count(1) from REPORTS exports where EXAMS.E_SOURCE = '门诊' AND exports.IS_ABNORMAL = '1') 门诊阳性例数,
(select count(1) from EXAM_ITEM item where EXAMS.E_SOURCE = '住院' AND EXAMS.E_NO = item.E_NO AND item.I_NAME = '项目A') 住院(项目A)人数,
(select count(1) from EXAM_ITEM item where EXAMS.E_SOURCE = '住院' AND EXAMS.E_NO = item.E_NO AND item.I_NAME = '项目B') 住院(项目B)人数,
(select count(1) from REPORTS exports where EXAMS.E_SOURCE = '住院' AND exports.IS_ABNORMAL = '1') 住院阳性例数
FROM EXAMS
GROUP BY EXAMS.E_DATE
ORDER BY EXAMS.E_DATE
EXAMS.E_DATE 检查时间,
sub1.cnt 门诊人数,
sub2.cnt 住院人数,
FROM EXAMS ,
(select count(1) cnt from EXAMS A where A.E_SOURCE = '门诊') sub1,
(select count(1) cnt from EXAMS B where B.E_SOURCE = '住院') sub2
Where
EXAMS.E_DATE = sub1.E_DATE AND EXAMS.E_DATE = sub2.E_DATE
GROUP BY EXAMS.E_DATE
ORDER BY EXAMS.E_DATE
他们是通过 E_NO关联的
select count(e_no) as '门诊人数',to_char(e_date,'yyyy-mm-dd') as '检查时间' ,a1 as '门诊(项目A)人数' ,a2 as '门诊(项目B)人数',a3 as '门诊阳性例数' from exams a,exam_item b where a.e_no=b.e_no and e_source='门诊'
left join (select count(e_no) from exam_item ) a1 on a.e_no=a1.e_no
left join (select count(e_no) from exam_item ) a2 on a.e_no=a1.e_no
left join (select count(IS_ABNORMAL) from reports where IS_ABNORMAL='0') a3 on a.e_no=a3.e_no
group by count(e_no),to_char(e_date,'yyyy-mm-dd'),a1,a2,a3 order by to_char(e_date,'yyyy-mm-dd');union select count(e_no) as '住院人数',to_char(e_date,'yyyy-mm-dd') as '检查时间' ,a1 as '住院(项目A)人数',a2 as '住院(项目B)人数',a3 as '住院阳性例数' from exams a,exam_item b where a.e_no=b.e_no and e_source='住院'
left join (select count(e_no) from exam_item ) a1 on a.e_no=a1.e_no
left join (select count(e_no) from exam_item ) a2 on a.e_no=a1.e_no
left join (select count(IS_ABNORMAL) from reports where IS_ABNORMAL='0') a3 on a.e_no=a3.e_no
group by count(e_no),to_char(e_date,'yyyy-mm-dd'),a1,a2,a3 order by to_char(e_date,'yyyy-mm-dd')
这是我想到的见笑了
select count(e_no) as '门诊人数',to_char(e_date,'yyyy-mm-dd') as '检查时间' ,a1 as '门诊(项目A)人数' ,a2 as '门诊(项目B)人数',a3 as '门诊阳性例数' from exams a,exam_item b where a.e_no=b.e_no and e_source='门诊'
left join (select count(e_no) from exam_item where i_name='项目A') a1 on a.e_no=a1.e_no
left join (select count(e_no) from exam_item where i_name='项目B') a2 on a.e_no=a1.e_no
left join (select count(IS_ABNORMAL) from reports where IS_ABNORMAL='0') a3 on a.e_no=a3.e_no
group by count(e_no),to_char(e_date,'yyyy-mm-dd'),a1,a2,a3 order by to_char(e_date,'yyyy-mm-dd');union select count(e_no) as '住院人数',to_char(e_date,'yyyy-mm-dd') as '检查时间' ,a1 as '住院(项目A)人数',a2 as '住院(项目B)人数',a3 as '住院阳性例数' from exams a,exam_item b where a.e_no=b.e_no and e_source='住院'
left join (select count(e_no) from exam_item where i_name='项目A') a1 on a.e_no=a1.e_no
left join (select count(e_no) from exam_item where i_name='项目B' ) a2 on a.e_no=a1.e_no
left join (select count(IS_ABNORMAL) from reports where IS_ABNORMAL='0') a3 on a.e_no=a3.e_no
group by count(e_no),to_char(e_date,'yyyy-mm-dd'),a1,a2,a3 order by to_char(e_date,'yyyy-mm-dd')
额 刚才的竟然犯了那么大的错误
select count(e_no) as '门诊人数',to_char(e_date,'yyyy-mm-dd') as '检查时间' ,a1 as '门诊(项目A)人数' ,a2 as '门诊(项目B)人数',a3 as '门诊阳性例数' from exams a,exam_item b where a.e_no=b.e_no and e_source='门诊'
left join (select count(e_no) from exam_item where i_name='项目A') a1 on a.e_no=a1.e_no
left join (select count(e_no) from exam_item where i_name='项目B') a2 on a.e_no=a2.e_no
left join (select count(IS_ABNORMAL) from reports where IS_ABNORMAL='0') a3 on a.e_no=a3.e_no
group by count(e_no),to_char(e_date,'yyyy-mm-dd'),a1,a2,a3 order by to_char(e_date,'yyyy-mm-dd');union select count(e_no) as '住院人数',to_char(e_date,'yyyy-mm-dd') as '检查时间' ,a1 as '住院(项目A)人数',a2 as '住院(项目B)人数',a3 as '住院阳性例数' from exams a,exam_item b where a.e_no=b.e_no and e_source='住院'
left join (select count(e_no) from exam_item where i_name='项目A') a1 on a.e_no=a1.e_no
left join (select count(e_no) from exam_item where i_name='项目B' ) a2 on a.e_no=a2.e_no
left join (select count(IS_ABNORMAL) from reports where IS_ABNORMAL='0') a3 on a.e_no=a3.e_no
group by count(e_no),to_char(e_date,'yyyy-mm-dd'),a1,a2,a3 order by to_char(e_date,'yyyy-mm-dd')
千锤百炼 百试不爽 不好意思 改了好几遍 试一下吧 我也是新手