mysql获取近7天，7周，7月，7年日期，根据当前时间获取近7天，7周，7月，7年日期

mysql获取近7天，7周，7月，7年日期，更具当前时间获取近7天，7周，7月，7年日期
废话不多说，直接看图
1：根据表中最大时间获取近7天日期

SELECT
        DATE_FORMAT(creattime, "%Y-%m-%d") b
    FROM t_stock_realtime_inventory
    WHERE
          DATE_FORMAT(creattime, "%Y-%m-%d") > (
      SELECT DATE_FORMAT(DATE_SUB((SELECT MAX(creattime) FROM t_stock_realtime_inventory), INTERVAL 7 DAY ),
                         "%Y-%m-%d") a
    GROUP BY a )
    GROUP BY
      b
    ORDER BY
      b ASC
1
2
3
4
5
6
7
8
9
10
11
12

1.1根据当前时间获取最近7日日期

SELECT
      date_sub( curdate( ), INTERVAL 7 DAY ) AS a UNION
      SELECT
      date_sub( curdate( ), INTERVAL 6 DAY ) AS a UNION
      SELECT
      date_sub( curdate( ), INTERVAL 5 DAY ) AS a UNION
      SELECT
      date_sub( curdate( ), INTERVAL 4 DAY ) AS a UNION
      SELECT
      date_sub( curdate( ), INTERVAL 3 DAY ) AS a UNION
      SELECT
      date_sub( curdate( ), INTERVAL 2 DAY ) AS a UNION
      SELECT
      date_sub( curdate( ), INTERVAL 1 DAY ) AS a
      FROM DUAL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2. 根据表中最大时间获取近7周日期

SELECT WEEK(creattime, 1) a
    FROM t_stock_realtime_inventory
    WHERE WEEK(creattime, 1) > (SELECT WEEK((SELECT MAX(creattime) FROM t_stock_realtime_inventory), 1)) - 7
    GROUP BY a
    ORDER BY a ASC
1
2
3
4
5

2.1根据当前时间获取最近7周日期

SELECT
      week(date_sub( curdate( ), INTERVAL 6 week ),1) AS a UNION
      SELECT
      week(date_sub( curdate( ), INTERVAL 5 week ),1) AS a UNION
      SELECT
      week(date_sub( curdate( ), INTERVAL 4  week),1) AS a UNION
      SELECT
      week(date_sub( curdate( ), INTERVAL 3 week ),1) AS a UNION
      SELECT
      WEEK(date_sub( curdate( ), INTERVAL 2 week ),1) AS a UNION
      SELECT
      week(date_sub( curdate( ), INTERVAL 1 week ),1) AS a UNION
      SELECT
      week( curdate( ), 1) AS a FROM DUAL
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.2获取近7周，每一周数据

SELECT
	D.dict_label lspzmc,
	D.dict_value lspzid,
CASE WHEN T.ccl IS NULL THEN 0 ELSE ROUND( SUM( T.CCL ), 4 ) END ccl 
FROM
	sys_dict_data D
	LEFT JOIN (
	SELECT
		b.dic_desc lspzmc,
		WEEK ( creat_time, 1 ) creat_time,
		 CASE WHEN sum( t.storage_qty ) IS NULL THEN 0 ELSE sum( t.storage_qty ) END ccl 
	FROM
		`t_stock_history` t
		INNER JOIN t_sys_dic b ON t.foodstuff_varieties_id = b.dic_id 
		WHERE
		DATE_SUB( CURDATE( ), INTERVAL 49 DAY ) <= DATE( t.creat_time ) -- 近7周内的数据故为 49
		AND WEEK ( creat_time, 1 ) = 17 -- 等于多少周
	GROUP BY
		b.dic_desc,
		creat_time 
	ORDER BY
		ccl DESC 
	) T ON T.lspzmc = D.dict_label 
WHERE
	dict_type = 'lslx' 
GROUP BY
	D.dict_label,
	D.dict_value
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

3. 根据表中最大时间获取近7月日期

SELECT
      DATE_FORMAT(creattime, "%Y-%m") b
    FROM t_stock_realtime_inventory
    WHERE
        DATE_FORMAT(creattime, "%Y-%m") > (
        SELECT DATE_FORMAT(DATE_SUB((SELECT MAX(creattime) FROM t_stock_realtime_inventory), INTERVAL 7 MONTH ),
                           "%Y-%m") a
    GROUP BY a )
    GROUP BY
      b
    ORDER BY
      b ASC
1
2
3
4
5
6
7
8
9
10
11
12

3.1根据当前时间获取最近7月日期

SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 6 month),"%Y-%m") UNION
      SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 5 month),"%Y-%m") UNION
      SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 4 month),"%Y-%m") UNION
      SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 3 month),"%Y-%m") UNION
      SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 2 month),"%Y-%m") UNION
      SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 month),"%Y-%m") UNION
      select DATE_FORMAT(NOW(),"%Y-%m") FROM DUAL
1
2
3
4
5
6
7

4. 根据表中最大时间获取近7年日期

SELECT DATE_FORMAT(creattime, "%Y") b
    FROM t_stock_realtime_inventory
    WHERE DATE_FORMAT(creattime, "%Y") > (
      SELECT DATE_FORMAT(DATE_SUB((SELECT MAX(creattime) FROM t_stock_realtime_inventory), INTERVAL 7 YEAR ), "%Y") a
    GROUP BY a )
    GROUP BY
      b
    ORDER BY
      b ASC
1
2
3
4
5
6
7
8
9

4.1根据当前时间获取最近7年日期

SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 6 YEAR),"%Y") UNION
      SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 5 YEAR),"%Y") UNION
      SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 4 YEAR),"%Y") UNION
      SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 3 YEAR),"%Y") UNION
      SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 2 YEAR),"%Y") UNION
      SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 YEAR),"%Y") UNION
      select DATE_FORMAT(NOW(),"%Y") FROM DUAL
1
2
3
4
5
6
7

如有参考，可根据自己业务逻辑修改参数查询

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦