dual mysql 获取序列_MySQL获取周、月、天日期，生成排序号

常用MySQL生成时间序列

--生成最近七天的日期，不包括当天

SELECT @cdate := date_add(@cdate, interval - 1 day) as date FROM(SELECT @cdate := date_add(date_sub(CURDATE(),interval 1 DAY), interval + 1 DAY) from resource_publish) t0 LIMIT 7;

2020-06-16

2020-06-15

2020-06-14

2020-06-13

2020-06-12

2020-06-11

2020-06-10

--生成最近七天的日期，包括当天

SELECT @cdate := date_add(@cdate, interval - 1 day) as date_str FROM(SELECT @cdate := date_add(CURDATE(), interval + 1 DAY) from resource_publish) t0 LIMIT 7;

2020-06-17

2020-06-16

2020-06-15

2020-06-14

2020-06-13

2020-06-12

2020-06-11

--生成最近七天的日期，包括当天

SELECT @cdate := date_add(@cdate, interval - 1 day) as date_str FROM(SELECT @cdate := date_add('202006017', interval + 1 DAY) from resource_publish) t0 LIMIT 7;

2020-06-17

2020-06-16

2020-06-15

2020-06-14

2020-06-13

2020-06-12

2020-06-11

--生成十二天每月的日期，包括当月

SELECT @cdate := date_add(@cdate, interval - 1 MONTH) as date_str FROM(SELECT @cdate := date_add(CURDATE(), interval + 1 MONTH) from resource_publish) t0 LIMIT 12;

2020-06-17

2020-05-17

2020-04-17

2020-03-17

2020-02-17

2020-01-17

2019-12-17

2019-11-17

2019-10-17

2019-09-17

2019-08-17

2019-07-17

-- 查询最近七个月每月日期

SELECT @cdate := date_add(@cdate, interval - 1 MONTH) as daytime FROM(SELECT @cdate := date_add(date_sub(CURDATE(),interval 1 MONTH), interval + 1 MONTH) from test_tb) t0 LIMIT 7;

2020-05-17

2020-04-17

2020-03-17

2020-02-17

2020-01-17

2019-12-17

2019-11-17

-- 查询最近六个月每月月末日期

SELECT @lastDay := last_day( date_add(@lastDay,interval 1 month )) lastDays from (SELECT @lastDay := date_add(curdate(),interval -6 month) from test_tb limit 6) a;

2020-01-31

2020-02-29

2020-03-31

2020-04-30

2020-05-31

2020-06-30

-- 查询最近七月的月份

SELECT date_format(@lastDay := last_day( d