这个就是每个省份 每年的建档孕妇数 SELECT ODS_D_PROVINCE.PROVINCE_ID as province_id, ODS_D_PROVINCE.PROVINCE as province_name, ODS_D_DIM_DATE.YEAR_1 as year, COUNT(ODS_F_GRAVIDA_RECORD.RECORD_ID) as val FROM ODS_D_PROVINCE, ODS_D_DIM_DATE, ODS_F_GRAVIDA_RECORD, ODS_D_HOSPITAL, ODS_D_CITY WHERE ( ODS_F_GRAVIDA_RECORD.HOSPITAL_NO=ODS_D_HOSPITAL.HOSPITAL_NO ) AND ( ODS_D_CITY.PROVINCE_ID=ODS_D_PROVINCE.PROVINCE_ID ) AND ( ODS_F_GRAVIDA_RECORD.INSERT_DATE=ODS_D_DIM_DATE.DATE_1 ) AND ( ODS_D_HOSPITAL.CITY_ID=ODS_D_CITY.CITY_ID(+) ) AND ODS_D_DIM_DATE.YEAR_1=2016 GROUP BY ODS_D_PROVINCE.PROVINCE_ID, ODS_D_PROVINCE.PROVINCE, ODS_D_DIM_DATE.YEAR_1 --------------------------------------------------- 这个就是每个省份 每年的高危孕妇数 SELECT ODS_D_PROVINCE.PROVINCE_ID as province_id, ODS_D_PROVINCE.PROVINCE as province_name, ODS_D_DIM_DATE.YEAR_1 as year, COUNT(ODS_F_GRAVIDA_RECORD.RECORD_ID) as val FROM ODS_D_PROVINCE, ODS_D_DIM_DATE, ODS_F_GRAVIDA_RECORD, ODS_F_EXAMINE_HISTORY, ODS_D_HOSPITAL, ODS_D_CITY WHERE ( ODS_F_GRAVIDA_RECORD.RECORD_ID=ODS_F_EXAMINE_HISTORY.RECORD_ID(+) ) AND ( ODS_F_GRAVIDA_RECORD.HOSPITAL_NO=ODS_D_HOSPITAL.HOSPITAL_NO ) AND ( ODS_D_CITY.PROVINCE_ID=ODS_D_PROVINCE.PROVINCE_ID ) AND ( ODS_F_GRAVIDA_RECORD.INSERT_DATE=ODS_D_DIM_DATE.DATE_1 ) AND ( ODS_D_HOSPITAL.CITY_ID=ODS_D_CITY.CITY_ID(+) ) AND ( ODS_F_EXAMINE_HISTORY.EXAMINE_HISTORY_NUM = 1 AND ODS_F_EXAMINE_HISTORY.HIGH_RISK_FACTOR NOT IN ( '健康','健康,无' ) ) AND ODS_D_DIM_DATE.YEAR_1=2016 GROUP BY ODS_D_PROVINCE.PROVINCE_ID, ODS_D_PROVINCE.PROVINCE, ODS_D_DIM_DATE.YEAR_1 ------------------------------------------------------------------------ 根据省ID，年份查询和地市（1-12月）的建档孕妇数 ------------------------------------------------------------------------ 根据省ID，年份查询和地市的高危孕妇数（高危颜色、高危值对应的数量） ------------------------------------------------------ 1. 查询每个省当前的高危总人数 SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_F_EXAMINE_HISTORY B, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.RECORD_ID=B.RECORD_ID AND A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND B.INSERT_DATE=E.DATE_1 AND A.NOW_WEEKS<42 AND B.HIGH_RISK_FACTOR<>'健康' AND B.HIGH_RISK_FACTOR IS NOT NULL AND B.EXAMINE_HISTORY_NUM=1 AND B.INSERT_DATE<=TRUNC(SYSDATE) GROUP BY D.PROVINCE_ID, D.PROVINCE ------------------------------------------------------ 1.1. 查询每个省内各地区当前的高危总人数 SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_F_EXAMINE_HISTORY B, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.RECORD_ID=B.RECORD_ID AND A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND B.INSERT_DATE=E.DATE_1 AND B.HIGH_RISK_FACTOR<>'健康' AND B.HIGH_RISK_FACTOR IS NOT NULL AND A.NOW_WEEKS<42 AND B.EXAMINE_HISTORY_NUM=1 AND B.INSERT_DATE<=TRUNC(SYSDATE) AND D.PROVINCE_ID=130000 GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY ------------------------------------------------------ 1.2. 查询每个地区内各区县当前的高危总人数 SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, D.AREA_COUNTY_ID as area_id, D.AREA_COUNTY as area_name, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_F_EXAMINE_HISTORY B, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.RECORD_ID=B.RECORD_ID AND A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND B.INSERT_DATE=E.DATE_1 AND A.NOW_WEEKS<42 AND B.HIGH_RISK_FACTOR<>'健康' AND B.HIGH_RISK_FACTOR IS NOT NULL AND B.EXAMINE_HISTORY_NUM=1 AND B.INSERT_DATE<=TRUNC(SYSDATE) AND D.PROVINCE_ID=130000 AND D.CITY_ID=130300 GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY, D.AREA_COUNTY_ID, D.AREA_COUNTY ------------------------------------------------------ 2. 查询每个省近30天的建档总人数 SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND A.CREATE_DATE=E.DATE_1 AND A.CREATE_DATE<=TRUNC(SYSDATE) AND A.CREATE_DATE>=(TRUNC(SYSDATE)-30) GROUP BY D.PROVINCE_ID, D.PROVINCE ------------------------------------------------------ 2.1. 查询每个省内各地区近30天的建档总人数 SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND A.CREATE_DATE=E.DATE_1 AND D.PROVINCE_ID=130000 AND A.CREATE_DATE<=TRUNC(SYSDATE) AND A.CREATE_DATE>=(TRUNC(SYSDATE)-30) GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY ------------------------------------------------------ 2.2. 查询每个地区内各区县近30天的建档总人数 SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, D.AREA_COUNTY_ID as area_id, D.AREA_COUNTY as area_name, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND A.CREATE_DATE=E.DATE_1 AND D.PROVINCE_ID=130000 AND D.CITY_ID=130300 AND A.CREATE_DATE<=TRUNC(SYSDATE) AND A.CREATE_DATE>=(TRUNC(SYSDATE)-30) GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY, D.AREA_COUNTY_ID, D.AREA_COUNTY ------------------------------------------------------ 3. 按省或市或地区查询区域内当前高危总数（按颜色分组、按高危项分组） SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, D.AREA_COUNTY_ID as area_id, D.AREA_COUNTY as area_name, F.HIGH_RISK_GROUP as group_name, F.HIGH_RISK_CONTENT as risk_name, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_F_EXAMINE_HISTORY B, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E, ODS_F_HIGH_RISK_VALUE F WHERE A.RECORD_ID=B.RECORD_ID AND A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND B.INSERT_DATE=E.DATE_1 AND B.EXAMINE_ID=F.EXAMINE_ID AND A.NOW_WEEKS<42 AND F.HIGH_RISK_CONTENT<>'健康' AND B.EXAMINE_HISTORY_NUM=1 AND E.YEAR_1=2016 AND D.PROVINCE_ID=130000 AND D.CITY_ID=130300 GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY, D.AREA_COUNTY_ID, D.AREA_COUNTY, F.HIGH_RISK_GROUP, F.HIGH_RISK_CONTENT ------------------------------------------------------ 4. 按省或市或地区查询区域内近12个自然月每月建档人数 SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, D.AREA_COUNTY_ID as area_id, D.AREA_COUNTY as area_name, E.YEAR_MONTH as year, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND A.CREATE_DATE=E.DATE_1 AND D.PROVINCE_ID=130000 AND D.CITY_ID=130300 AND D.AREA_COUNTY_ID=130302 AND A.CREATE_DATE<=TRUNC(SYSDATE) AND A.CREATE_DATE>=TRUNC(ADD_MONTHS(SYSDATE,-11)) GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY, D.AREA_COUNTY_ID, D.AREA_COUNTY, E.YEAR_MONTH ORDER BY E.YEAR_MONTH ------------------------------------------------------ 4.1. 按省或市或地区查询区域内近12个自然周每周建档人数（如不能按周就同4.2查询近90天按天的） SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, D.AREA_COUNTY_ID as area_id, D.AREA_COUNTY as area_name, E.DATE_1 as ymd, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND A.CREATE_DATE=E.DATE_1 AND D.PROVINCE_ID=130000 AND D.CITY_ID=130300 AND D.AREA_COUNTY_ID=130302 AND A.CREATE_DATE<=TRUNC(SYSDATE) AND A.CREATE_DATE>=(TRUNC(SYSDATE)-91) GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY, D.AREA_COUNTY_ID, D.AREA_COUNTY, E.DATE_1 ORDER BY E.DATE_1 ------------------------------------------------------ 4.2. 按省或市或地区查询区域内近30天每天建档人数 SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, D.AREA_COUNTY_ID as area_id, D.AREA_COUNTY as area_name, E.DATE_1 as ymd, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND A.CREATE_DATE=E.DATE_1 AND D.PROVINCE_ID=130000 AND D.CITY_ID=130300 AND D.AREA_COUNTY_ID=130302 AND A.CREATE_DATE<=TRUNC(SYSDATE) AND A.CREATE_DATE>=(TRUNC(SYSDATE)-30) GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY, D.AREA_COUNTY_ID, D.AREA_COUNTY, E.DATE_1 ORDER BY E.DATE_1 ------------------------------------------------------ 5. 按省或市或地区查询区域内近12个自然月每月初诊人数、复诊人数 SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, D.AREA_COUNTY_ID as area_id, D.AREA_COUNTY as area_name, E.YEAR_MONTH as year, SUM(DECODE(B.EXAMINE_CATEGORY_ID,1,1,0)) AS val, SUM(DECODE(B.EXAMINE_CATEGORY_ID,2,1,0)) AS val2 FROM ODS_F_GRAVIDA_RECORD A, ODS_F_EXAMINE_HISTORY B, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.RECORD_ID=B.RECORD_ID AND A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND B.INSERT_DATE=E.DATE_1 AND B.INSERT_DATE<=TRUNC(SYSDATE) AND B.INSERT_DATE>=TRUNC(ADD_MONTHS(SYSDATE,-11)) AND D.PROVINCE_ID=130000 AND D.CITY_ID=130300 AND D.AREA_COUNTY_ID=130302 GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY, D.AREA_COUNTY_ID, D.AREA_COUNTY, E.YEAR_MONTH ORDER BY E.YEAR_MONTH ------------------------------------------------------ 5.1. 按省或市或地区查询区域内近12个自然周每周初诊人数、复诊人数（如不能按周就同5.2查询近90天按天的） SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, D.AREA_COUNTY_ID as area_id, D.AREA_COUNTY as area_name, E.DATE_1 as ymd, SUM(DECODE(B.EXAMINE_CATEGORY_ID,1,1,0)) AS val, SUM(DECODE(B.EXAMINE_CATEGORY_ID,2,1,0)) AS val2 FROM ODS_F_GRAVIDA_RECORD A, ODS_F_EXAMINE_HISTORY B, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.RECORD_ID=B.RECORD_ID AND A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND B.INSERT_DATE=E.DATE_1 AND B.INSERT_DATE<=TRUNC(SYSDATE) AND B.INSERT_DATE>=(TRUNC(SYSDATE)-90) AND D.PROVINCE_ID=130000 AND D.CITY_ID=130300 AND D.AREA_COUNTY_ID=130302 GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY, D.AREA_COUNTY_ID, D.AREA_COUNTY, E.DATE_1 ORDER BY E.DATE_1 ------------------------------------------------------ 5.2. 按省或市或地区查询区域内近30天每天初诊人数、复诊人数 SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, D.AREA_COUNTY_ID as area_id, D.AREA_COUNTY as area_name, E.DATE_1 as ymd, SUM(DECODE(B.EXAMINE_CATEGORY_ID,1,1,0)) AS val, SUM(DECODE(B.EXAMINE_CATEGORY_ID,2,1,0)) AS val2 FROM ODS_F_GRAVIDA_RECORD A, ODS_F_EXAMINE_HISTORY B, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E WHERE A.RECORD_ID=B.RECORD_ID AND A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND B.INSERT_DATE=E.DATE_1 AND B.INSERT_DATE<=TRUNC(SYSDATE) AND B.INSERT_DATE>=(TRUNC(SYSDATE)-30) AND D.PROVINCE_ID=130000 AND D.CITY_ID=130300 AND D.AREA_COUNTY_ID=130302 GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY, D.AREA_COUNTY_ID, D.AREA_COUNTY, E.DATE_1 ORDER BY E.DATE_1 ------------------------------------------------------ 6. 按省或市或地区查询区域内近6个自然月每月产检中历史高危人数（按颜色分组） SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, D.AREA_COUNTY_ID as area_id, D.AREA_COUNTY as area_name, F.HIGH_RISK_GROUP as group_name, E.YEAR_MONTH as year, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_F_EXAMINE_HISTORY B, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E, ODS_F_HIGH_RISK_VALUE F WHERE A.RECORD_ID=B.RECORD_ID AND A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND B.INSERT_DATE=E.DATE_1 AND B.EXAMINE_ID=F.EXAMINE_ID AND F.HIGH_RISK_CONTENT<>'健康' AND F.HIGH_RISK_GROUP<>'其他' AND B.EXAMINE_HISTORY_NUM=1 AND B.INSERT_DATE<=TRUNC(SYSDATE) AND B.INSERT_DATE>=TRUNC(ADD_MONTHS(SYSDATE,-6)) AND D.PROVINCE_ID=130000 AND D.CITY_ID=130300 AND D.AREA_COUNTY_ID=130302 GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY, D.AREA_COUNTY_ID, D.AREA_COUNTY, F.HIGH_RISK_GROUP, E.YEAR_MONTH ORDER BY F.HIGH_RISK_GROUP,E.YEAR_MONTH ------------------------------------------------------- 6.1. 按省或市或地区查询区域内近30天每天产检中历史高危人数（按颜色分组） SELECT D.PROVINCE_ID as province_id, D.PROVINCE as province_name, D.CITY_ID as city_id, D.CITY as city_name, D.AREA_COUNTY_ID as area_id, D.AREA_COUNTY as area_name, F.HIGH_RISK_GROUP as group_name, E.DATE_2 as year, COUNT(A.RECORD_ID) AS val FROM ODS_F_GRAVIDA_RECORD A, ODS_F_EXAMINE_HISTORY B, ODS_D_HOSPITAL C, ODS_D_AREA_COUNTY D, ODS_D_DIM_DATE E, ODS_F_HIGH_RISK_VALUE F WHERE A.RECORD_ID=B.RECORD_ID AND A.HOSPITAL_NO=C.HOSPITAL_NO AND C.AREA_COUNTY_ID=D.AREA_COUNTY_ID AND B.INSERT_DATE=E.DATE_1 AND B.EXAMINE_ID=F.EXAMINE_ID AND F.HIGH_RISK_CONTENT<>'健康' AND F.HIGH_RISK_GROUP<>'其他' AND B.EXAMINE_HISTORY_NUM=1 AND B.INSERT_DATE<=TRUNC(SYSDATE) AND B.INSERT_DATE>=(TRUNC(SYSDATE)-30) AND D.PROVINCE_ID=130000 AND D.CITY_ID=130300 AND D.AREA_COUNTY_ID=130302 GROUP BY D.PROVINCE_ID, D.PROVINCE, D.CITY_ID, D.CITY, D.AREA_COUNTY_ID, D.AREA_COUNTY, F.HIGH_RISK_GROUP, E.DATE_2 ORDER BY F.HIGH_RISK_GROUP,E.DATE_2;