shiyang / regional-platform

sql.txt 1.74 KB
edit raw blame history 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
这个就是每个省份 每年的建档孕妇数

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