How to generate consecutive dates and variable assignments in mysql

1. Production consecutive dates

Explanation: Mainly used for some statistical data to display in chronological order;
If the database data has data for the next day, the statistics will not If it needs to be displayed every day, even if it is 0, then a timetable must be generated for use;

Query database data:

SELECT
        DATE_FORMAT( create_time, '%Y-%m-%d' ) AS date,
        COUNT(1) AS numb
FROM
    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' )

Output the next day Data:

Doesn’t this look good? If you have to add an order by 1 desc to arrange it, you can also...
But even if When discharged, the time is also inconsistent;

Generate the dates of the last 7 days:

// 方法笨，但还是有效的
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) t1,  -- 假如嫌弃生成的少，打来这个就行
            (SELECT @num:=0) y) as timeList

Generate time:

Use after generation:

-- 生成后左右链接即可，但主表要为时间表
SELECT f.datetime, t.numb FROM 
(SELECT
        DATE_FORMAT( create_time, '%Y-%m-%d' ) AS date,
        COUNT(1) AS numb
FROM
    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' )) t
right 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 ;

After use:

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

2. Variable assignment

You see there are many @ symbols in the first one, right? Those are all variables. If you put the query statement in, the entire query statement will be too bloated and will be very long...so some things can be split out;

For example, in the query database statement:

Let’s take a look at their true faces:

#  查询部门区域编号
SELECT @dep_BH:=(SELECT area_code FROM qc_department WHERE dept_name = "历下区");
#  查询部门等级
SELECT @dep_DJ:=(SELECT dept_level FROM qc_department WHERE dept_name = "历下区");

The above is the detailed content of How to generate consecutive dates and variable assignments in mysql. For more information, please follow other related articles on the PHP Chinese website!