求sql语句

查询条件：2011-11-01~2011-11-08

原始数据

  create_time count
2011-11-08 18:26:12 2
2011-11-08 19:26:12 2
2011-11-08 20:26:12 2
2011-11-06 18:26:12 1
2011-11-05 18:26:12 3
2011-11-03 18:26:12 2


结果：
2011-11-08 6(8号的全部相加)
2011-11-07 0
2011-11-06 1
2011-11-05 3
2011-11-04 0
2011-11-03 2
2011-11-02 0
2011-11-01 0

SQL code

select date(create_time),sum(count)
from 
(
    select * from 原始数据
    union all
    select '2011-11-01' ,0
    union all
    select '2011-11-02' ,0
    union all
    select '2011-11-03' ,0
    union all
    select '2011-11-04' ,0
    union all
    select '2011-11-05' ,0
    union all
    select '2011-11-06' ,0
    union all
    select '2011-11-07' ,0
    union all
    select '2011-11-08' ,0
) t
group by date(create_time)
order by 1 desc

更常见的设计方法是另外设计一个 日历表 (cdate date primary key) 然后其中INSERT入一年的所有日期。
之后再 
SQL code

select c.cdate,sum(d.count)
from 日历表 c left join 原始数据 d on c.cdate=d.create_time
where c.cdate between '2011-11-01' and '2011-11-08'
group by c.cdate

第二种方法比较好