select
a.*,
b.*
from
(
SELECT CONCAT(YEAR(NOW()),'01') AS date UNION
SELECT CONCAT(YEAR(NOW()),'02') AS date UNION
SELECT CONCAT(YEAR(NOW()),'03') AS date UNION
SELECT CONCAT(YEAR(NOW()),'04') AS date UNION
SELECT CONCAT(YEAR(NOW()),'05') AS date UNION
SELECT CONCAT(YEAR(NOW()),'06') AS date UNION
SELECT CONCAT(YEAR(NOW()),'07') AS date UNION
SELECT CONCAT(YEAR(NOW()),'08') AS date UNION
SELECT CONCAT(YEAR(NOW()),'09') AS date UNION
SELECT CONCAT(YEAR(NOW()),'10') AS date UNION
SELECT CONCAT(YEAR(NOW()),'11') AS date UNION
SELECT CONCAT(YEAR(NOW()),'12') AS date
)a
left join
(SELECT
count(*) AS total,
SUM(字段= '1') AS auto_metering ,
SUM(字段= '2') AS auto_distribution,
SUM(字段= '3') AS charging_pile,
SUM(字段 = '4') AS trans_detection,
SUM(字段= '5') AS volta_detection,
SUM(字段= '6') AS comp_business ,
SUM(字段= '7') AS not_Maintained ,
stat_month as month
FROM
表where del_flag = '0' and 1=1
) b
on a.date = b.month
b.month 的时间格式是202001,如果数据库存的是2020-01 那么需要把
SELECT CONCAT(YEAR(NOW()),'01') AS date UNION 更改为:
SELECT CONCAT(YEAR(NOW()),'-01') AS date UNION