当前位置:   article > 正文

MySQL时间查询讲解+实战教学(查询本月、上个月、下个月等等的数据)_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博客_数据库时间

本文内容由网友自发贡献,转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号