mysql统计时间补零_MySQL 查询某时间段范围内的数据 补零

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

好 查看结果

#------------完