按月统计上报情况

按照部门、过去12个月做统计,区分已上报数，本月无数据，已发送提醒;

 当月无数据留0

自己写的最啰嗦最长最没效率的SQL...

SELECT
cc.com_id AS com_id,
v.`month` AS `month`,
cc.com_name AS com_name,
cc.user_id AS user_id,
IFNULL(t1.count, 0) AS `monthly_count`,
IFNULL(t2.nodata,0) AS no_data,
IFNULL(t2.sent,0) AS sent
FROM
(
	SELECT date_format(curdate(),'%Y-%m') AS `month`
	union SELECT date_format((curdate() - interval 1 month),'%Y-%m') AS `month`
	union SELECT date_format((curdate() - interval 2 month),'%Y-%m') AS `month`
	union SELECT date_format((curdate() - interval 3 month),'%Y-%m') AS `month`
	union SELECT date_format((curdate() - interval 4 month),'%Y-%m') AS `month`
	union SELECT date_format((curdate() - interval 5 month),'%Y-%m') AS `month`
	union SELECT date_format((curdate() - interval 6 month),'%Y-%m') AS `month`
	union SELECT date_format((curdate() - interval 7 month),'%Y-%m') AS `month`
	union SELECT date_format((curdate() - interval 8 month),'%Y-%m') AS `month`
	union SELECT date_format((curdate() - interval 9 month),'%Y-%m') AS `month`
	union SELECT date_format((curdate() - interval 10 month),'%Y-%m') AS `month`
	union SELECT date_format((curdate() - interval 11 month),'%Y-%m') AS `month`
) AS v
INNER JOIN com_company AS cc
INNER JOIN sys_user AS su ON su.USER_ID = cc.com_id
LEFT JOIN (
	SELECT
	DATE_FORMAT(cp.create_date, '%Y-%m') AS MONTH,
	COUNT(CASE WHEN cp.com_personam_user_uuid NOT IN ('-99', '-100') THEN 1  ELSE null END) AS count,
	cp.com_company_id AS com_company_id
	FROM
	com_personam AS cp
	WHERE
	DATE_FORMAT(cp.create_date, '%Y-%m') > DATE_FORMAT(
	date_sub(curdate(), INTERVAL 12 MONTH),
	'%Y-%m'
)
GROUP BY
cp.com_company_id,
MONTH
) AS t1 ON v.`month` = t1.`month`
AND t1.com_company_id = cc.com_id
LEFT JOIN (
	SELECT DISTINCT
	com_company_id,
	DATE_FORMAT(create_date,'%Y-%m') AS `month`,
	CASE WHEN com_personam_user_uuid = '-100' THEN DATE_FORMAT(create_date,'%Y-%m') ELSE null END AS sent,
	CASE WHEN com_personam_user_uuid = '-99' THEN DATE_FORMAT(create_date,'%Y-%m') ELSE null END AS nodata
	FROM
	com_personam
	WHERE
	com_personam_user_uuid = '-99' OR com_personam_user_uuid = '-100'
) AS t2 ON v.`month` = t2.`month` AND t2.com_company_id = cc.com_id
WHERE
su.ROLE_ID = 'JRJG'
-- AND v.`month` >= '2017-01'
GROUP BY
cc.com_id,
v.`MONTH`
ORDER BY
`MONTH` DESC