数据库 MySQL时间查询讲解+实战教学（查询本月、上个月、下个月等等的数据）

MySql时间查询

MySql查询当前时间

查询年-月-日时:分:秒 select now()

查询年-月-日 select DATE(CURDATE())

查询年-月 select date_format(NOW(),'%Y-%m')

查询当前年 select YEAR(CURDATE())

查询当前月 select MONTH(CURDATE())

查询当前日 select DAYOFMONTH(NOW())

查询当前星期几 select dayofweek(NOW()) -1

查询当前季度 select QUARTER(now())

查询时:分:秒 select current_time()

万能公式：DATE_FORMAT select DATE_FORMAT(now(),'%Y-%m-%d %h:%i:%s')

MySql查询上一年、上一个月、上一个星期…

查询昨天的数据 select * from test as 'time' where time.update_time

MySql查询本年、本月、本周…

查询今天的数据 select * from test as time where to_days(time.update_time ) =to_days(now()); 查询本周的数据 select * FROM test as time WHERE YEARWEEK(date_format(time.update_time ,'%Y-%m-%d')) = YEARWEEK(now()); 查询本月的数据 select * FROM test as time WHERE DATE_FORMAT(time.update_time , '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) 查询本季度的数据 select * from test as time where QUARTER(time.update_time)=QUARTER(now()); 查询本年的数据 select * from test as time where YEAR(time.update_time)=YEAR(NOW());

实战：生成这样的一个数据表，根据月份来输出值

有两个关键的数据，一个是月份，一个是数据，我们要的效果，每个月累加（比如6月份有一个人，但7月份没有，但是是累加的，所以7月份有一个，12月份新增三个，所以总共是4）

思考：

第一点：月份：要通过本月来向前面递减，重点用UNION连接函数

-- 近1个月

SELECT DATE_FORMAT( CURDATE( ), '%Y-%m' ) AS `month` UNION

-- 近2个月

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m' ) AS `month` UNION

-- 近3个月

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m' ) AS `month` UNION

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m' ) AS `month` UNION

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m' ) AS `month` UNION

-- 近N个月,N等于"INTERVAL 5 MONTH"中间的数值`5`加`1`，此处N=6

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m' ) AS `month`

整理一下，因为后续我们还连接数据，所以要用v来指代这个数据

SELECT CONCAT(RIGHT(v.`month`,2),'月') AS `time`

FROM

(

-- 近1个月

SELECT DATE_FORMAT( CURDATE( ), '%Y-%m' ) AS `month` UNION

-- 近2个月

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m' ) AS `month` UNION

-- 近3个月

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m' ) AS `month` UNION

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m' ) AS `month` UNION

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m' ) AS `month` UNION

-- 近N个月,N等于"INTERVAL 5 MONTH"中间的数值`5`加`1`，此处N=6

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m' ) AS `month`

)

AS v

第二点：数据：计算近六个月的数据（如下）

SELECT DATE_FORMAT(a.create_time, '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

-- 计算近六个月的时间

WHERE DATE_FORMAT(a.create_time, '%Y-%m') > DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH ), '%Y-%m')

-- 补充查询匹配条件

AND a.is_deleted = 1

GROUP BY `month`

但是这样是不对的，这个是分别查询六个月的数据，不是累加的（需要这种的朋友上面代码自取），所以我们要改一下思路

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 0 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

-- 计算12月及其之前的时间（现在2022年12月份 DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m') = 2022-11）

-- 这是是需要计算一月份之前，要包含12月份

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL -1 MONTH ), '%Y-%m')

-- 补充查询匹配条件

AND a.is_deleted = 1

好了，但这只有一条数据，我们需要再累加一下

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 0 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

-- 计算12月及其之前的时间（现在2022年12月份 DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m') = 2022-11）

-- 这是是需要计算一月份之前，要包含12月份

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL -1 MONTH ), '%Y-%m')

-- 补充查询匹配条件

AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION

-- 下面都是cv了，sql没想到什么好的办法，但在mybatis中可以优化，之后我会讲到

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 0 MONTH ), '%Y-%m')

AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m')

AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m')

AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m')

AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m')

AND a.is_deleted = 1 AND a.jurisdiction = 2

把之前的结合一下

SELECT IFNULL( b.count, 0 ) AS `count`,CONCAT(RIGHT(v.`month`,2),'月') AS `time`

FROM

(

-- 近1个月

SELECT DATE_FORMAT( CURDATE( ), '%Y-%m' ) AS `month` UNION

-- 近2个月

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m' ) AS `month` UNION

-- 近3个月

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m' ) AS `month` UNION

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m' ) AS `month` UNION

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m' ) AS `month` UNION

-- 近N个月,N等于"INTERVAL 5 MONTH"中间的数值`5`加`1`，此处N=6

SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m' ) AS `month`

)

AS v LEFT JOIN

(

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 0 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

-- 计算12月及其之前的时间（现在2022年12月份 DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m') = 2022-11）

-- 这是是需要计算一月份之前，要包含12月份

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL -1 MONTH ), '%Y-%m')

-- 补充查询匹配条件

AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION

-- 下面都是cv了，sql没想到什么好的办法，但在mybatis中可以优化，之后我会讲到

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 0 MONTH ), '%Y-%m')

AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m')

AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m')

AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m')

AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`

FROM `user` AS a

WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m')

AND a.is_deleted = 1 AND a.jurisdiction = 2

) AS b

ON v.`month` = b.`month`

GROUP BY `time` ORDER BY `time` ASC

放入mybatis中优化一下

实体类:

@Data

@Accessors(chain = true)

@AllArgsConstructor

@NoArgsConstructor

public class PeopleCountVO {

private Integer count;

private String time;

private Integer jurisdiction;

private List math;

}

xml:根据math的数据动态的修改，比如math=[1,2,3,4,5,6,7,8,9,10,11,12]，就是查询12个月的数据

参考文章：数据库查询当前时间大全（MySQL、Oracle、Sqlserver）_小豆的编程世界…的博客-CSDN博客_数据库时间

参考链接

评论可见，请评论后查看内容，谢谢！！！

您阅读本篇文章共花了：

金钥匙

数据库 MySQL时间查询讲解+实战教学（查询本月、上个月、下个月等等的数据）

数据库 mysql sql select into from 和 insert into select的使用

spring boot oracle java SpringBoot项目从mysql数据源改造为kingbase数据源的一些常见sql问题

发表评论取消回复

金钥匙

数据库 MySQL时间查询讲解+实战教学（查询本月、上个月、下个月等等的数据）

数据库 mysql sql select into from 和 insert into select的使用

spring boot oracle java SpringBoot项目从mysql数据源改造为kingbase数据源的一些常见sql问题

相关文章

发表评论取消回复