收到业务需求需要分析各业务类型本月和上月的数据对比，查询数据的过程中主要需要处理一个问题：需要将本月、上月产生数据的业务类型全部显示，但有一部分上月产生数据的业务类型在本月还没有产生数据

1. 将时间字段格式化为“2020-01”格式

DATE_FORMAT(CREATE_DATE_, '%Y-%m')

2. 为数据源添加“上月”时间标识

SELECTDATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,DATE_FORMAT(date_sub(CREATE_DATE_,interval 1 MONTH), '%Y-%m') AS LAST_DATE_,a.MESSAGE_,COUNT(a.ID_) AS NUM_
FROMt_project_comment a
WHEREa.STATUS_ > 0
GROUP BYDATE_,MESSAGE_
ORDER BYDATE_,MESSAGE_

3. 利用left join将“数据源”用时间和类型连接，获取当前时间业务数据及对应的上月业务数据

SELECTa.DATE_,a.MESSAGE_,a.NUM_,a.LAST_DATE_,IFNULL(b.NUM_, 0) AS LAST_NUM_
FROM(SELECTDATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,DATE_FORMAT(date_sub(CREATE_DATE_,INTERVAL 1 MONTH),'%Y-%m') AS LAST_DATE_,a.MESSAGE_,COUNT(a.ID_) AS NUM_FROMt_project_comment aWHEREa.STATUS_ > 0GROUP BYDATE_,MESSAGE_ORDER BYDATE_,MESSAGE_) a
LEFT JOIN (SELECTDATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,DATE_FORMAT(date_sub(CREATE_DATE_,INTERVAL 1 MONTH),'%Y-%m') AS LAST_DATE_,a.MESSAGE_,COUNT(a.ID_) AS NUM_FROMt_project_comment aWHEREa.STATUS_ > 0GROUP BYDATE_,MESSAGE_ORDER BYDATE_,MESSAGE_
) b ON a.LAST_DATE_ = b.DATE_
AND a.MESSAGE_ = b.MESSAGE_

4. 为数据源添加“下月”时间标识

SELECTDATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,DATE_FORMAT(date_add(CREATE_DATE_,interval 1 MONTH), '%Y-%m') AS NEXT_DATE_,a.MESSAGE_,COUNT(a.ID_) AS NUM_
FROMt_project_comment a
WHEREa.STATUS_ > 0
GROUP BYDATE_,MESSAGE_
ORDER BYDATE_,MESSAGE_

5. 利用left join将“数据源”用时间和类型连接，获取当前时间业务数据及对应的下月业务数据
   注意：DATE_为NEXT_DATE_

SELECTa.NEXT_DATE_ AS DATE_,a.MESSAGE_,IFNULL(b.NUM_, 0) AS NUM_,a.DATE_ AS LAST_DATE_,IFNULL(a.NUM_, 0) AS LAST_NUM_
FROM(SELECTDATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,DATE_FORMAT(date_add(CREATE_DATE_,INTERVAL 1 MONTH),'%Y-%m') AS NEXT_DATE_,a.MESSAGE_,COUNT(a.ID_) AS NUM_FROMt_project_comment aWHEREa.STATUS_ > 0GROUP BYDATE_,MESSAGE_ORDER BYDATE_,MESSAGE_) a
LEFT JOIN (SELECTDATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,DATE_FORMAT(date_add(CREATE_DATE_,INTERVAL 1 MONTH),'%Y-%m') AS NEXT_DATE_,a.MESSAGE_,COUNT(a.ID_) AS NUM_FROMt_project_comment aWHEREa.STATUS_ > 0GROUP BYDATE_,MESSAGE_ORDER BYDATE_,MESSAGE_
) b ON a.NEXT_DATE_ = b.DATE_
AND a.MESSAGE_ = b.MESSAGE_

6. 用union将“上月数据表”和“下月数据表”合并

SELECTa.DATE_,a.MESSAGE_,a.NUM_,a.LAST_DATE_,IFNULL(b.NUM_, 0) AS LAST_NUM_
FROM(SELECTDATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,DATE_FORMAT(date_sub(CREATE_DATE_,INTERVAL 1 MONTH),'%Y-%m') AS LAST_DATE_,a.MESSAGE_,COUNT(a.ID_) AS NUM_FROMt_project_comment aWHEREa.STATUS_ > 0GROUP BYDATE_,MESSAGE_ORDER BYDATE_,MESSAGE_) a
LEFT JOIN (SELECTDATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,DATE_FORMAT(date_sub(CREATE_DATE_,INTERVAL 1 MONTH),'%Y-%m') AS LAST_DATE_,a.MESSAGE_,COUNT(a.ID_) AS NUM_FROMt_project_comment aWHEREa.STATUS_ > 0GROUP BYDATE_,MESSAGE_ORDER BYDATE_,MESSAGE_
) b ON a.LAST_DATE_ = b.DATE_
AND a.MESSAGE_ = b.MESSAGE_
UNIONSELECTa.NEXT_DATE_ AS DATE_,a.MESSAGE_,IFNULL(b.NUM_, 0) AS NUM_,a.DATE_ AS LAST_DATE_,IFNULL(a.NUM_, 0) AS LAST_NUM_FROM(SELECTDATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,DATE_FORMAT(date_add(CREATE_DATE_,INTERVAL 1 MONTH),'%Y-%m') AS NEXT_DATE_,a.MESSAGE_,COUNT(a.ID_) AS NUM_FROMt_project_comment aWHEREa.STATUS_ > 0GROUP BYDATE_,MESSAGE_ORDER BYDATE_,MESSAGE_) aLEFT JOIN (SELECTDATE_FORMAT(CREATE_DATE_, '%Y-%m') AS DATE_,DATE_FORMAT(date_add(CREATE_DATE_,INTERVAL 1 MONTH),'%Y-%m') AS NEXT_DATE_,a.MESSAGE_,COUNT(a.ID_) AS NUM_FROMt_project_comment aWHEREa.STATUS_ > 0GROUP BYDATE_,MESSAGE_ORDER BYDATE_,MESSAGE_) b ON a.NEXT_DATE_ = b.DATE_AND a.MESSAGE_ = b.MESSAGE_

Ps：因为mysql没有全外连接，所以需要用union代替