查询表中近六个月的数据每个月的数据

最新推荐文章于 2022-12-26 11:10:57 发布

原创最新推荐文章于 2022-12-26 11:10:57 发布 · 779 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

该报告统计了过去六个月的社区类型分布情况，包括居民区（juJia）和机构区（jiGou）。通过LEFT JOIN操作从日期、患者和社区表中获取数据，按月份汇总，展示了每个月两种社区类型的数量。最新月份的数据排在最前，揭示了社区构成的变化趋势。

SELECT six_month.month as `month`,
     sum( IF(slcom.community_type = 0, 1, 0) ) as juJia,
      sum( IF(slcom.community_type = 1 , 1, 0 ) ) as jiGou
     from
    (
     SELECT DATE_FORMAT(NOW(), '%Y-%m') month FROM DUAL

    UNION ALL SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m') month FROM DUAL
    UNION ALL SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 2 MONTH), '%Y-%m') month FROM DUAL
    UNION ALL SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 MONTH), '%Y-%m') month FROM DUAL
    UNION ALL SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 4 MONTH), '%Y-%m') month FROM DUAL
    UNION ALL SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 5 MONTH), '%Y-%m') month FROM DUAL

    ) six_month LEFT JOIN sl_patient as pa

    on DATE_FORMAT(pa.create_time,'%Y-%m') = six_month.month

    LEFT JOIN  sl_community as slcom on slcom.id =pa.community_id
    GROUP BY  six_month.month
   ORDER BY six_month.month ASC