需求：查询最近30天每天的业绩总和(当天没有业绩的默认为0)

1.刚开始我的sql是这样写的：

SELECT truncate(sum(af2.money),2) as m,DATE_FORMAT(af2.create_date,'%Y-%m-%d') as gptime from financial_flow af2,vip_student vs1

where af2.ACCOUNT_NO IN ('XFSR','XFBK') and DATE(af2.create_date)>=DATE_SUB(CURDATE(), INTERVAL 29 DAY) and af2.stu_id=vs1.id and vs1.subject_name='JAVA' GROUP BY gptime

查询结果：

发现，少了15号的数据，因为我表里面根本没有这一天的数据，在这里也不可能用IFNULL()函数补全。

相关函数解析 ：

1>ROUND(x)与ROUND(x，y)与truncate(x,y)

ROUND(x)函数返回最接近于参数x的整数，对x值进行四舍五入。

SELECT ROUND(-2.34),ROUND(-4.56),ROUND(2.34),ROUND(4.56);

ROUND(x,y)函数返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。

SELECT ROUND(3.45,1),ROUND(3.45,0),ROUND(123.45,-1),ROUND(167.8,-2);

truncate(x,y)函数返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。、

SELECT TRUNCATE(2.34,1),TRUNCATE(4.56,1),TRUNCATE(4.56,0),TRUNCATE(56.78,-1);ROUND(x,y)函数在截取值的时候会四舍五入，而TRUNCATE(x,y)函数直接截取值，并不进行四舍五入。

2>DATE_FORMAT(date,format)函数用于以不同的格式显示日期/时间数据。

date参数是合法的日期。format规定日期/时间的输出格式。

format可选常用格式：%y--年，2 位|%Y--年，4

位%m--月，数值(00-12)|%M--月名

%e--月的天，数值(0-31)|%d--月的天，数值(00-31)

%h--小时 (01-12)|%H--小时 (00-23)

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')

DATE_FORMAT(NOW(),'%m-%d-%Y')

DATE_FORMAT(NOW(),'%d %b %y')

DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

Dec 29 2008 11:45 PM

12-29-2008

29 Dec 08

29 Dec 2008 16:25:46.635

3>DATE(date)函数返回日期或日期/时间表达式的日期部分。date参数是合法的日期表达式。

DATE(2017-8-01 16:24:33)----------结果就是：2017-8-01

4>DATE_SUB与DATE_ADD

mysql中内置函数date_add和date_sub能对指定的时间进行增加或减少一个指定的时间间隔，语法如下：

DATE_ADD(date,INTERVAL expr type)DATE_SUB(date,INTERVAL expr type)

其中date是指定的日期，INTERVAL为关键词，expr是具体的时间间隔，type是时间单位。注意：type可以复合型的，比如 YEAR_MONTH。

如果type不是复合型的，DATE_ADD和DATE_SUB其实可以通用，因为expr可以为一个负数。常用type如下图：

5>IFNULL(expr1,expr2)，如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。

IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。

2.接着上面的需求说，刚刚的sql不能满足预期结果，换一种思路：

可以生成最近30天的日期然后left join业绩表，这样就是那天没有业绩也能用IFNULL()函数设置为0

了。

生成最近30天的日期序列：

select date_sub(CURDATE(),interval @i:=@i+1 day) as date

from (select 1 union all  select 1 union all  select 1 union all select 1 union all select 1 union all select 1 union all  select 1 union all select 1

union all select 1 union all select 1 union all  select 1 union all select 1 union all select 1 union all select 1 union all  select 1 union all select 1

union all select 1 union all select 1 union all  select 1 union all select 1 union all select 1 union all select 1 union all  select 1 union all select 1

union all select 1 union all select 1 union all  select 1 union all select 1 union all select 1 union all select 1) as tmp,

(select @i:= -1) t

结果(30条数据)：

select 1 union all select 1 union all select 1 union all select 1·

是为了生成一个一列N行的虚拟表，然后由表t与其做笛卡尔积，这样根据N行会生成n行的一个时间序列。

最后拿生成的日期序列与业绩表做左连接：

SELECT lefttable.date,IFNULL(righttable.m,'0') as money

FROM

(SELECT date_sub(CURDATE(),interval @i:=@i+1 day) as date

from ( select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select

1 union all select 1 union all select 1 union all   select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all  select 1 union all select 1 union all select 1 union all select 1 union

all select 1 union all select 1 union all select 1 union all  select 1 union all select 1) as tmp,(select @i:= -1) t) as lefttable

LEFT JOIN

(SELECT truncate(sum(af2.money),2) as m,DATE_FORMAT(af2.create_date,'%Y-%m-%d') as gptime

from financial_flow af2,vip_student vs1

where af2.ACCOUNT_NO IN ('XFSR','XFBK') and DATE(af2.create_date)>=DATE_SUB(CURDATE(), INTERVAL 29 DAY) and af2.stu_id=vs1.id and vs1.subject_name='JAVA' GROUP BY gptime)

as righttable

ON

lefttable.date=righttable.gptime

查询结果：

达到了预期结果。

0-0 这是目前我能找到的能实现此效果的方法，我想肯定还有更好的方式，慢慢学习。