DBA SQL开发知识：mysql 生成连续日期及变量
1、生产连续日期
说明：主要作用于一些统计数据，来根据时间顺序进行显示；
假如数据库数据有隔天数据，偏偏统计又需要每天的都显示，即便是0，那就要生成一个时间表，来使用；
查询数据库数据：
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’ )
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’ )
隔天时出数据：
这样是不是就不好看了，如果非要说加个order by 1 desc 排一下，也可以…
但是就算排出来，时间也是不连贯的；
生成最近7天的日期：
// 方法笨，但还是有效的
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
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
生成的时间：
生成后使用：
— 生成后左右链接即可，但主表要为时间表
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 ;
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 ;
使用后效果：
我这里是数据库没有数据…所以看不到有数量显示
2、变量赋值
看到第一个里面 @符号很多是吗，那些都是变量，如果把查询语句放进去，整个查询语句就显得太臃肿了，会很长…所以有些东西可以拆分出去；
比如在查询数据库语句中的：
来看下他们的真面目：
# 查询部门区域编号
SELECT @dep_BH:=(SELECT area_code FROM qc_department WHERE dept_name = “历下区”);
# 查询部门等级
SELECT @dep_DJ:=(SELECT dept_level FROM qc_department WHERE dept_name = “历下区”);
SELECT @dep_BH:=(SELECT area_code FROM qc_department WHERE dept_name = “历下区”);
# 查询部门等级
SELECT @dep_DJ:=(SELECT dept_level FROM qc_department WHERE dept_name = “历下区”);
到此这篇关于mysql 生成连续日期及变量赋值的文章就介绍到这了,更多相关mysql 连续日期与变量赋值内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
