统计一段时间内每天的各种数据,发现某天没有的数据没法显示出来,所以这时候用一张日历表去联合查询即能获得每天的数据
创建日历表用到ADDDATE这个系统函数,这个函数会自动为给定的日期添加指定的时间间隔,
ADDDATE(‘2018-01-01’,1) 会得出2018-01-01,利用这个功能,给指定的日期循环添加比如10000,即能得到指定日期后10000天的数据
CREATE TABLE num (i int);-- 创建一个表用来储存0-9的数字
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);-- 生成0-9的数字,方便以后计算时间
CREATE TABLE if not exists calendar(datelist date); -- 生成一个存储日期的表,datalist是字段名
INSERT INTO calendar(datelist) SELECT
-- 利用下面计算出0-10000的数字依次添加指定时间间隔,即得2018-01-01后10000天的日历表
ADDDATE(
( -- 这里的起始日期,你可以换成当前日期
DATE_FORMAT("2018-01-01", '%Y-%m-%d')
),
numlist.id
) AS `date`
FROM
(
--- 这边利用笛卡尔积计算出 0 - 10000 依次各个数据
SELECT
n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 AS id
FROM
num n1
CROSS JOIN num AS n10
CROSS JOIN num AS n100
CROSS JOIN num AS n1000
) AS numlist;
– 还可以按月增加日历表
INSERT INTO calendar(monthlist) SELECT
ADDDATE(
( -- 这里的起始日期,你可以换成当前日期
DATE_FORMAT("2026-05-01", '%Y-%m-%d')
),
interval numlist.id month
) AS date
FROM
(
-- 这边利用笛卡尔积计算出 0 - 100000 依次各个数据
SELECT
n1.i + n10.i * 10 AS id
FROM
num n1
CROSS JOIN num AS n10
) AS numlist;
将日历表创建好后,然后在需要查询的时候,用union all,如果calendar字段少的话,用null或者0代替缺少的字段
egg:
-
select l.date,ifnull(l.volume,0) as volume,ifnull(l.weight,0) as weight,ifnull(l.num,0) as num from ("+selSql+ " union all select date_format(cal.datelist,'%Y%m%d') as dday,null,null,null from calendar cal where cal.datelist BETWEEN :startDate and :endDate ) l group by l.date ";
2.时间字段两种情况:合格/不合格(Y|N)
UNION ALL SELECT 'N',date_format(cal.datelist,'%Y%m%d') as dday,null,null FROM calendar cal where cal.datelist BETWEEN :start_date and :end_date
UNION ALL SELECT 'Y',date_format(cal.datelist,'%Y%m%d') as dday,null,null FROM calendar cal where cal.datelist BETWEEN :start_date and :end_date
最后别忘了group by date,否则会有重复日期。