[MySQL]递归生成数据行

来源：https://baijiahao.baidu.com/s?id=1744215294693190607&wfr=spider&for=pc

 

DEMO：

WITH RECURSIVE
cte AS(
	SELECT 1 as n UNION
	SELECT n+1 FROM cte WHERE n<30
)
SELECT * FROM cte;

 

递归生成最近30天

WITH
last30day AS (
	WITH RECURSIVE
    cte AS(
		SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS date
		UNION
		SELECT DATE_SUB(date, INTERVAL 1 DAY) AS date FROM cte
		WHERE DATEDIFF(CURDATE(), date) <= 30
	)
	SELECT * FROM cte
	ORDER BY date ASC
)
SELECT * FROM last30day;