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

CxToBj

于 2021-08-12 16:40:21 发布

阅读量693

点赞数

本文链接：https://blog.csdn.net/lang09/article/details/119647988

版权

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