mysql 查询每日、每月、每年数据

monody666

已于 2022-10-21 13:59:13 修改

阅读量1.3k

点赞数

分类专栏： java知识积累文章标签： mysql 数据库 sql java 后端

于 2022-10-21 13:35:54 首次发布

本文链接：https://blog.csdn.net/monody666/article/details/127444669

版权

java知识积累专栏收录该内容

59 篇文章 0 订阅

订阅专栏

每日
SELECT *
FROM
  (SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Date
   FROM
     (SELECT 0 AS a
      UNION ALL SELECT 1
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 7
      UNION ALL SELECT 8
      UNION ALL SELECT 9) AS a CROSS
   JOIN
     (SELECT 0 AS a
      UNION ALL SELECT 1
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 7
      UNION ALL SELECT 8
      UNION ALL SELECT 9) AS b CROSS
   JOIN
     (SELECT 0 AS a
      UNION ALL SELECT 1
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 7
      UNION ALL SELECT 8
      UNION ALL SELECT 9) AS c) a

每月
SELECT *
FROM
  (SELECT DATE_FORMAT(curdate() - INTERVAL (a.a + (10 * b.a)) MONTH,'%Y-%m')  AS Date
   FROM
     (SELECT 0 AS a
      UNION ALL SELECT 1
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 7
      UNION ALL SELECT 8
      UNION ALL SELECT 9) AS a CROSS
   JOIN
     (SELECT 0 AS a
      UNION ALL SELECT 1
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 7
      UNION ALL SELECT 8
      UNION ALL SELECT 9) AS b) a

每年
SELECT *
FROM
  (SELECT DATE_FORMAT(curdate() - INTERVAL (a.a ) YEAR,'%Y')  AS Date
   FROM
     (SELECT 0 AS a
      UNION ALL SELECT 1
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 7
      UNION ALL SELECT 8
      UNION ALL SELECT 9) AS a) a

后面关联其他表即可实现数据查询

加WHERE即可实现时间段查询，如下，其它时间段同理


每日，最近十个月
DATE_SUB(now(), INTERVAL 10 MONTH)

每月，最近三个月
DATE_FORMAT(DATE_SUB( now(), INTERVAL 3 MONTH),'%Y-%m')

每年，最近两年
DATE_FORMAT(DATE_SUB( now(), INTERVAL 2 YEAR),'%Y')