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

This article mainly introduces how mysql generates continuous dates and variable assignment, which has certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

1. Continuous date of production

Description: Mainly acts on some statistical data to display according to time sequence;

If the database data has the next day data, but statistics need to be displayed every day, even if it is 0, it is necessary to generate a timetable to use;

Query database data:

SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) AS date, COUNT(1) AS numbFROM qc_task WHERE create_time>= DATE_SUB(CURDATE(),INTERVAL 1 MONTH) and department_id IN ( SELECT id FROM `vigilante_jinan`.` qc_department` WHERE `area_code` = (@dep_BH) AND `status` = '1' AND dept_level >= (@dep_DJ)) GROUP BY DATE_FORMAT( create_time, '%Y-%m-%d' )

Next day's data:

This is not good to see, if you have to say add an order by 1 desc, you can also…

But even if it is discharged, time is incoherent;

Date of last 7 days generated:

//method stupid, but still valid select DATE_FORMAT(SUBDATE(NOW(), interval (timeList.sj*1460) MINUTE),'%Y-%m-% d') as 'datetime' from (SELECT @num:=@num+1 as sj from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) t, -- (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) (SELECT @num:=0) y) as timeList

Time generated:

Use after generation:

--After generation, link left and right, but the main table should be the timetable SELECT f.datetime, t. num FROM (SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) AS date, COUNT(1) AS numbFROM qc_task WHERE create_time>= DATE_SUB(CURDATE(),INTERVAL 1 MONTH) and department_id IN ( SELECT id FROM `vigilante_jinan`.` qc_department` WHERE `area_code` = (@dep_BH) AND `status` = '1' AND dept_level >= (@dep_DJ)) GROUP BY DATE_FORMAT( create_time, '%Y-%m-%d' )) tright join (select DATE_FORMAT(SUBDATE(NOW(), interval (timeList.sj*1460) MINUTE),'%Y-%m-%d') as 'datetime' from (SELECT @num:=@num+1 as sj from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) t, (SELECT @num:=0) y) as timeList) f on t.date = f.datetime ;

Effect after use:

I have no data in the database here…so I can't see the quantity display

2. Variable assignment

See a lot of @ symbols in the first one, those are variables, if you put the query statement in, the whole query statement will be too bloated, it will be very long…so some things can be split out;

For example, in the query database statement:

Look at their true faces:

#Query department area number SELECT @dep_BH:=(SELECT area_code FROM qc_department WHERE dept_name = "Lixia District");#Query department level SELECT @dep_DJ:=(SELECT dept_level FROM qc_department WHERE dept_name = "Lixia District"); Thank you for reading this article carefully. I hope that the article "mysql how to generate continuous dates and variable assignment" shared by Xiaobian will be helpful to everyone. At the same time, I hope everyone will support you more and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

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.