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.根据当前时间获得最近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

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

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

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

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

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

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

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

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