如何在sql中實現(xiàn)日期自動補全

這篇文章將為大家詳細講解有關(guān)如何在sql中實現(xiàn)日期自動補全，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

最近在開發(fā)統(tǒng)計訪問功能時，如果數(shù)據(jù)表里缺少某一天的數(shù)據(jù)，就會導致那天的記錄丟失，但顯示的時候還是需要那天的數(shù)據(jù)，沒有的話，可以默認為 0，大概想到了兩種解決辦法：

1. 將數(shù)據(jù)讀出來后，在程序循環(huán)添加缺少的數(shù)據(jù)，但是這對于統(tǒng)計數(shù)據(jù)范圍可變的需求，可能統(tǒng)計 7 天，30 天，60 天；通過程序來補全數(shù)據(jù)就顯得有點麻煩了。

2. 首先生成某個范圍的日歷表數(shù)據(jù)，再通過這個日歷表連接查詢需要統(tǒng)計數(shù)據(jù)表的數(shù)據(jù)，沒有的話，默認為 0；這樣就可以得出這個范圍內(nèi)完整的數(shù)據(jù)了，所以這種方案，更適合這種需求的變化。

現(xiàn)在通過如下 sql 語句生成日歷表：

CREATE TABLE num (i INT);-- 創(chuàng)建一個表用來儲存0-9的數(shù)字
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);-- 生成0-9的數(shù)字，方便以后計算時間
CREATE TABLE  IF NOT EXISTS calendar(DATE DATE); -- 生成一個存儲日期的表，date是字段名
-- 這里是生成并插入日期數(shù)據(jù)
INSERT INTO calendar(DATE) SELECT
    ADDDATE(
        (   -- 起始日期，從2017-1-1開始
            DATE_FORMAT("2017-1-1", '%Y-%m-%d')
        ),
        numlist.id
    ) AS `date`
FROM
    (
        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;

執(zhí)行 sql 后生成 1W 條記錄，從2017-01-01~2044-05-18，這些數(shù)據(jù)已經(jīng)足夠用很久了；也可以通過修改相對應的條件，生成更多的數(shù)據(jù)，這里就不詳細說明了，根據(jù)需求修改就行。

執(zhí)行如下 sql 測試：

SELECT COUNT(u.id) AS num, c.date FROM calendar AS c
LEFT JOIN users AS u ON c.date = u.`created_at`
WHERE c.date BETWEEN '2017-05-01' AND '2017-05-07'
GROUP BY c.`date`
ORDER BY c.`date`

得出如下結(jié)果：

關(guān)于如何在sql中實現(xiàn)日期自動補全就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。