mysql获取近7天，7周，7月，7年日期，根据当前时间获取近7天，7周，7月，7年日期,获取近几月每月的最后一天的日期

刺骨的言语ヽ痛彻心扉 2023-09-29 12:01 14阅读 0赞

**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

**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

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

**5.获取近几月每月的最后一天的日期**

SELECT date_format(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())-10, 0)),'yyyy-MM-dd')
    union all
    SELECT date_format(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())-9, 0)),'yyyy-MM-dd')
    union all
    SELECT date_format(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())-8, 0)),'yyyy-MM-dd')
    union all
    SELECT date_format(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())-7, 0)),'yyyy-MM-dd')
    union all
    SELECT date_format(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())-6, 0)),'yyyy-MM-dd')
    union all
    SELECT date_format(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())-5, 0)),'yyyy-MM-dd')
    union all
    SELECT date_format(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())-4, 0)),'yyyy-MM-dd')
    union all
    SELECT date_format(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())-3, 0)),'yyyy-MM-dd')
    union all
    SELECT date_format(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())-2, 0)),'yyyy-MM-dd')
    union all
    SELECT date_format(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())-2, 0)),'yyyy-MM-dd')
    union all
    SELECT date_format(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()), 0)),'yyyy-MM-dd')
    union all
    select date_format(now(),'yyyy-MM-dd')