MySQL时间查询讲解+实战教学（查询本月、上个月、下个月等等的数据）_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 <DATE_SUB(CURDATE(), INTERVAL 1 DAY)

• 查询当前这周的数据

  select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 0 WEEK)

  select * FROM test as 'time' where YEARWEEK(date_format(time.update_time,’%Y-%m-%d’)) = YEARWEEK(now())

• 查询上一周的数据
  select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 WEEK)

  select * FROM test as 'time' WHERE YEARWEEK(date_format(time.update_time,’%Y-%m-%d’)) = YEARWEEK(now())-1

• 查询上一个月的数据
  select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

• 查询近两个月的数据（INTERVAL后面的数字改一下就可以了）
  select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 2 MONTH)

• 查询上一年的数据
  select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

• 查询距离当前现在6个月的数据

  select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

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`
1
2
3
4
5
6
7
8
9
10

整理一下，因为后续我们还连接数据，所以要用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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

 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`
1
2
3
4
5
6
7

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

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
1
2
3
4
5
6
7

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

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

把之前的结合一下

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
AND a.is_deleted = 1  AND a.jurisdiction = 2
      ) AS b 
    ON v.`month` = b.`month`
GROUP BY `time` ORDER BY `time` ASC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

放入mybatis中优化一下

实体类:

@Data
@Accessors(chain = true)
@AllArgsConstructor
@NoArgsConstructor
public class PeopleCountVO {
    private Integer count;
    private String time;

    private Integer jurisdiction;
    private List<Integer> math;
}
1
2
3
4
5
6
7
8
9
10
11

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

    <select id="peopleCount" parameterType="com.zhao.pojo.vo.PeopleCountVO" resultType="com.zhao.pojo.vo.PeopleCountVO">
        SELECT  IFNULL( b.count, 0 ) AS `count`,v.`month` AS `time`
        FROM
        (
            -- 查询 近六个月
            <foreach  collection="people.math" separator=" UNION " item="item">
                SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL #{item} - 1 MONTH ), '%Y-%m' ) AS `month`
            </foreach>
        )
        AS v LEFT JOIN
        (
            <foreach collection="people.math" separator=" UNION " item="item">
                SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL #{item}-1 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`
                FROM `user` AS a
                -- 小于符号 在xml中会报错 使用 &lt; 转义一下
                WHERE DATE_FORMAT(a.create_time, '%Y-%m') &lt; DATE_FORMAT(( CURDATE( ) - INTERVAL #{item}-2 MONTH ), '%Y-%m')
                -- 补充查询匹配条件
                AND a.is_deleted = 1 AND a.jurisdiction = #{people.jurisdiction}
            </foreach>
        ) AS b
        ON v.`month` = b.`month`
        GROUP BY `time` ORDER BY `time` ASC
    </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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