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中如何實現日期自動補全的詳細內容。更多資訊請關注PHP中文網其他相關文章！