mysql查询半年内的数据数量报表格式为1月：20.........

//查询一个表的半年内的数据

SELECT
                concat(t1.month,"月") as signingMonth,
                IFNULL(count(1),0) AS signingNum
                
            FROM
                (
            SELECT  MONTH(DATE_FORMAT(CURDATE(), '%Y-%m-%h')) as month
                UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%h')) AS month 
                UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%h')) AS month
                UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%h')) AS month
                UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%h')) AS month 
                UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m-%h')) AS month 
                ) t1
            LEFT JOIN test_signing AS ts ON (
                MONTH(DATE_FORMAT(ts.create_time, '%Y-%m-%h'))= t1.month and YEAR(ts.create_time)=YEAR(NOW())
            )
            GROUP BY t1.month

 

 

 

//多张表查询半年内的数据 然后合并

 select a.month1 as month,a.totalElectricElectricity,b.totalChargingElectricity
        FROM (SELECT
            concat(t1.month,"月") as month1,
            IFNULL(SUM(ts.electricity),0) AS totalElectricElectricity
            
        FROM
            (
        SELECT  MONTH(DATE_FORMAT(CURDATE(), '%Y-%m-%h')) as month
            UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%h')) AS month 
            UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%h')) AS month
            UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%h')) AS month
            UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%h')) AS month 
            UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m-%h')) AS month 
            ) t1
        LEFT JOIN test_car_statistical AS ts ON (
            MONTH(DATE_FORMAT(ts.create_time, '%Y-%m-%h'))= t1.month and ts.type=1 and YEAR(ts.create_time)=YEAR(NOW())
        )
        GROUP BY t1.month) as a 
        
        LEFT JOIN 
        (
        SELECT concat(t2.month,"月") as month2,IFNULL(SUM(tc.electricity),0) AS totalChargingElectricity  
        FROM(
                SELECT  MONTH(DATE_FORMAT(CURDATE(), '%Y-%m-%h')) as month
                    UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%h')) AS month 
                    UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%h')) AS month
                    UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%h')) AS month
                    UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%h')) AS month 
                    UNION SELECT MONTH(DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m-%h')) AS month 
                    ) t2
                LEFT JOIN test_car_statistical AS tc ON (
                    MONTH(DATE_FORMAT(tc.create_time, '%Y-%m-%h'))= t2.month and tc.type=2 and YEAR(tc.create_time)=YEAR(NOW())
                )
                GROUP BY  t2.month
                
                ) as b
        ON b.month2=a.month1

 

效果