1.创建基础表
CREATE TABLE num (i INT); INSERT INTO num (i) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2.查询时间范围
SELECT adddate('2015-11-25', numlist.id) AS 'date' FROM ( SELECT n1.i + n10.i * 10 + n100.i * 100 AS id FROM num n1 CROSS JOIN num AS n10 CROSS JOIN num AS n100 ) AS numlist WHERE adddate('2015-11-25', numlist.id) <= '2015-12-25';
3 创建数据表
create table datatable(tDate char(10) PRIMARY KEY,tCount int(3)); insert into datatable(tDate,tCount)values ('2015-11-24',23),('2015-11-25',1), ('2015-11-26',21),('2015-11-28',17), ('2015-11-29',13),('2015-12-01',5), ('2015-12-02',11),('2015-12-04',8), ('2015-12-05',3),('2015-12-07',29), ('2015-12-09',80),('2015-12-10',24)
4 查询 (无值时补零)
SELECT a.date, COALESCE (b.tcount, 0) count FROM ( SELECT adddate('2015-11-25', numlist.id) AS 'date' FROM ( SELECT n1.i + n10.i * 10 + n100.i * 100 AS id FROM num n1 CROSS JOIN num AS n10 CROSS JOIN num AS n100 ) AS numlist WHERE adddate('2015-11-25', numlist.id) <= '2015-12-25' ) a LEFT JOIN datatable b ON a.date = b.tDate
好 查看结果
#------------完