如何在sql中實現日期自動補全

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

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

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

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

現在通過如下 sql 語句生成日歷表：

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(DATE DATE); -- 生成一個存儲日期的表，date是字段名
-- 這里是生成并插入日期數據
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;

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

執行如下 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`

得出如下結果：

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