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.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
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
如有参考，可根据自己业务逻辑修改参数查询
