需求：查询最近三个月的每一天的业绩总和

因为最近三个月每个月的天数是不一样，所以不能用这篇文章：Mysql查询最近30天的数据（每天的业绩总和数据） 介绍的用固定多少天去查数据。需要一个新方法。

一、生成每个月的每一天的时间序列

SELECT ADDDATE(y.first, x.d - 1) as d 
FROM 
    (SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL 
    SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
    SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL 
    SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL 
    SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) x, 
    (SELECT CONCAT('2017-09','-01') as FIRST, DAY(LAST_DAY(str_to_date('2017-09','%Y-%m'))) AS last) y 
WHERE x.d <= y.last
   
    

结果：

二、用上面的时间序列与目标结果集做左连接

SELECT DATE_FORMAT(lefttable.date,'%Y-%c') as yearMonth,lefttable.date,IFNULL(righttable.m,'0') as money 
FROM  
        (SELECT ADDDATE(y.first, x.d - 1) as date 
FROM 
(SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL 
SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL 
SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL 
SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) x, 
(SELECT CONCAT('2017-8','-01') as FIRST, DAY(LAST_DAY(str_to_date('2017-8','%Y-%c'))) AS last) y 
WHERE x.d <= y.last and ADDDATE(y.first, x.d - 1)<=CURDATE()) as lefttable 
LEFT JOIN  
        (SELECT truncate(sum(af2.money),2) as m,DATE_FORMAT(af2.create_date,'%Y-%c-%d') as gptime  
                from financial_flow af2,vip_student vs1  
                where af2.ACCOUNT_NO IN ('XFSR','XFBK') and DATE_FORMAT(af2.create_date,'%Y-%c')='2017-8' and af2.stu_id=vs1.id and vs1.subject_name='JAVA' GROUP BY gptime) 
         as righttable 
ON  
DATE_FORMAT(lefttable.date,'%Y-%c-%d')=righttable.gptime
   
    

结果：

达到需求目标。

原文地址：IT虾米网