最近写查询遇到这样一个情况。我需要查到一段时间（比如2015-06-15到2015-07-01）内每一天的总价格。
可当我写sql查询的时候，发现如果一天没有内容，那么这一天的数据将不会被sql查出来，意味着从开始到结束的日期中可能会有中断。

查询.sql
1
2
3
select DATE_FORMAT( cast(created_at as date), '%Y-%m-%d') as date, count(*) as num from 
`order` where `id` = 3  and `created_at`  between '2015-06-15' and '2015-07-01' 
group by cast(created_at as date)

这样的查询结果如下：

1
2
3
4
5
6
7
8
9
2015-06-22	2
2015-06-23	25
2015-06-24	60
2015-06-25	41
2015-06-26	65
2015-06-27	72
2015-06-28	81
2015-06-29	87
2015-06-30	59

发现中间没有数据的日期没被正确填充。

在经过一番了解之后找到了一个比较靠谱的做法，如下：
首先根据这个 PROCEDURE 创建一个PROCEDURE 并插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DROP PROCEDURE IF EXISTS FillCalendar;
DROP TABLE IF EXISTS calendar;
CREATE TABLE IF NOT EXISTS calendar(date DATE NOT NULL PRIMARY KEY);

DELIMITER $$
    CREATE PROCEDURE FillCalendar(start_date DATE, end_date DATE)
    BEGIN
    DECLARE crt_date DATE;
    SET crt_date = start_date;
    WHILE crt_date <= end_date DO
        INSERT IGNORE INTO calendar VALUES(crt_date);
        SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY);
    END WHILE;
    END$$
DELIMITER ;

CALL FillCalendar('2000-01-01', '2050-12-31');// 这里插入的是需要用的时间段，我在calendar表中插入了2000~2050年的数据

插入完成之后就可以将calendar表和order表进行连接，在order表中缺少的字段，默认取0。这里应用了一个SQL的函数 COALESCE

插入完后calendar表内容：

1
2
3
4
5
6
7
date
----------
2000-01-01
2000-01-02
...
2015-12-30
2050-12-31

COALESCE函数的作用

返回其参数中第一个非空表达式

更新后的sql
1
2
3
4
5
6
7
8
select a.date ,COALESCE(b.num, 0) num from calendar a 
left join
(
select DATE_FORMAT( cast(created_at as date), '%Y-%m-%d') as date, count(*) as `num` from `order` where `id` = 3 and `created_at`  between '2015-06-15' and '2015-07-01' group by cast(created_at as date)
) b
on b.date=a.date
where a.`date` between '2015-06-15' and '2015-07-01'
order by a.`date` asc

这样得到的输出：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2015-06-15	0
2015-06-16	0
2015-06-17	0
2015-06-18	0
2015-06-19	0
2015-06-20	0
2015-06-21	0
2015-06-22	2
2015-06-23	25
2015-06-24	60
2015-06-25	41
2015-06-26	65
2015-06-27	72
2015-06-28	81
2015-06-29	87
2015-06-30	59
2015-07-01	0

这个问题一开始纠结了比较久，原因是在找有没有不加calendar表能做到同样的效果。还有那个COALESCE函数的用法。