統(tǒng)計數(shù)據(jù)時，如果交易數(shù)據(jù)不是連續(xù)的，比如統(tǒng)計每天的銷售額，某個日期對應節(jié)假日，銷售額=0，這樣輸出的數(shù)據(jù)是不連續(xù)的。為了解決這個問題，我們可以提前生成一個日期表，沒有數(shù)據(jù)的日期填充0即可。
具體步驟如下：

1、創(chuàng)建一個num表，用來存儲數(shù)字0-9

create table num(i int);

2、在num表生成0-9

insert into num(i) values(0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

3、生成一個存儲日期的表，datelist是字段名

create table if not exists calendar(datelist date);

4、插入日期數(shù)據(jù)

INSERT INTO calendar
    (datelist
    )
SELECT
    adddate((date_format('2015-01-01', '%Y-%m-%d')), numlist.id) AS DATE,
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 + n10000.i * 10000 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
        CROSS JOIN num AS n1000
        CROSS JOIN num AS n10000
    ) AS numlist;

5、最后，添加主鍵

ALTER TABLE `calendar`
ADD COLUMN `id`  int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵' FIRST ,
ADD PRIMARY KEY (`id`);

到這里，日期表已經(jīng)建好了，統(tǒng)計數(shù)據(jù)的時候關聯(lián)該表即可。

附：tidb創(chuàng)建日期表語句示例

-- 創(chuàng)建一個num表，用于存儲0-9
drop table if EXISTS test.temp_num;
CREATE TABLE test.temp_num (
`i`  int(10) NOT NULL 
)
;
INSERT INTO test.temp_num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
select * from test.temp_num;

-- 創(chuàng)建日期表
drop table if EXISTS test.temp_calendar;
CREATE TABLE test.temp_calendar (
`id`  int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`datelist`  date NULL DEFAULT NULL 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_bin
ROW_FORMAT=COMPACT 
;

-- 插入日期數(shù)據(jù)
INSERT INTO test.temp_calendar
    (datelist
    )
SELECT
    adddate((date_format('2015-01-01', '%Y-%m-%d')), numlist.id1) AS DATE
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 + n10000.i * 10000 AS id1
        FROM
            test.temp_num n1
        CROSS JOIN test.temp_num AS n10
        CROSS JOIN test.temp_num AS n100
        CROSS JOIN test.temp_num AS n1000
        CROSS JOIN test.temp_num AS n10000
        order by id1 asc 
    ) AS numlist;
select * from test.temp_calendar order by datelist desc limit 10;