Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to achieve automatic date completion in sql. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Recently, when developing the statistical access feature, if the data of a certain day is missing in the data table, the record of that day will be lost, but the data of that day is still needed for display. If not, it can be set to 0 by default. There are probably two solutions:

1. After reading the data, add the missing data in the program cycle, but for the need for a variable range of statistical data, it may be 7 days, 30 days, 60 days; it is a bit troublesome to complete the data through the program.

two。 First, generate a certain range of calendar data, and then use this calendar to connect and query the data that requires statistical tables. If not, the default is 0; in this way, you can get the complete data in this range, so this scheme is more suitable for changes in this demand.

Now generate the calendar with the following sql statement:

CREATE TABLE num (I INT);-- create a table to store 0-9 digits INSERT INTO num (I) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);-- generate 0-9 numbers to facilitate future calculation of time CREATE TABLE IF NOT EXISTS calendar (DATE DATE) -- generate a table that stores dates, where date is the field name-- here is the generation and insertion of date data INSERT INTO calendar (DATE) SELECT ADDDATE ((--start date) Starting from 2017-1-1, DATE_FORMAT ("2017-1-1",'% YMY% mMel% 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

1W records are generated after the execution of sql, from 2017-01-01-01 to 2044-05-18, which has been used for a long time. You can also modify the corresponding conditions to generate more data, which is not specified here, but can be modified as needed.

Perform the following sql test:

SELECT COUNT (u.id) AS num, c.date FROM calendar AS cLEFT JOIN users AS u ON c.date = u.`order _ at`where c.date BETWEEN '2017-05-01' AND' 2017-05-07'GROUP BY c.`date`order BY c.`date`

The results are as follows:

On how to achieve automatic date completion in sql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.