查询条件: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
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
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
之后再
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
BEGIN
SET @BEG='2011-01-01' ;
SET @END='2011-02-01' ;
DROP TABLE IF EXISTS LSB;
CREATE TABLE LSB(RQ DATE);
WHILE @BEG<=@END DO
INSERT INTO LSB VALUES(@BEG);
SET @BEG=@BEG+INTERVAL 1 DAY;
END WHILE;
END$$DELIMITER ;