How to implement automatic date completion in sql

The method to implement date automatic completion in sql: first generate a certain range of calendar table data; then query the data that requires statistical data tables through this calendar table connection; and finally obtain the complete data within this range, that is Can.

#Recently when developing the statistical access function, if the data for a certain day is missing from the data table, the record for that day will be lost, but it is still necessary to display it. If the data for that day is not available, it can be defaulted to 0. I think of two solutions:

1. After reading the data, add the missing data in the program loop, but this is a variable range for the statistical data. According to the demand, it may be counted for 7 days, 30 days, or 60 days; it is a bit troublesome to complete the data through the program.

2. First generate a certain range of calendar table data, and then use this calendar table connection to query the data in the statistical data table. If not, the default is 0; in this way, you can get the complete data in this range. , so this solution is more suitable for this change in demand.

Now generate the calendar table through the following sql statement:

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;

After executing the sql, 1W records are generated. From 2017-01-01~2044-05-18, these data are enough for a long time. ; You can also generate more data by modifying the corresponding conditions. This is not explained in detail here. Just modify it according to your needs.

Execute the following sql test:

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`

The following results are obtained:

The above is the detailed content of How to implement automatic date completion in sql. For more information, please follow other related articles on the PHP Chinese website!