MySQL创建日期视图的存储过程

MySQL创建日期视图的存储过程

1、创建一个最近n天日期视图的存储过程

创建一个最近n天的视图

-- 创建一个最近n天的视图
CREATE PROCEDURE price_by_day(IN n INT)
BEGIN
	DECLARE str VARCHAR(10000) DEFAULT CONCAT('CREATE OR REPLACE VIEW price_by_day AS SELECT DATE_ADD(CURDATE(),INTERVAL -', n, ' DAY) date');
	SET n := n - 1;
	WHILE n >= 0 DO
		SET str := CONCAT(str,' UNION ALL SELECT DATE_ADD(CURDATE(),INTERVAL -',n,' DAY) date');
		SET n := n - 1;
	END WHILE;
	SELECT str;
END

执行此存储过程

CALL price_by_day(30)

此存储过程会返回一个字符串，是一个创建视图的SQL语句

CREATE OR REPLACE VIEW price_by_day AS
SELECT DATE_ADD( CURDATE(), INTERVAL - 30 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 29 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 28 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 27 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 26 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 25 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 24 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 23 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 22 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 21 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 20 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 19 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 18 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 17 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 16 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 15 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 14 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 13 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 12 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 11 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 10 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 9 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 8 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 7 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 6 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 5 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 4 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 3 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 2 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 1 DAY ) date 
UNION ALL
SELECT DATE_ADD( CURDATE(), INTERVAL - 0 DAY ) date

查询视图

SELECT * FROM price_by_day

2、近12个月的视图

CREATE OR REPLACE VIEW `year_month_view` AS 
SELECT date_format( curdate(), '%Y-%m' ) AS `date` 
UNION ALL
SELECT date_format(( curdate() - INTERVAL 11 MONTH ), '%Y-%m' ) AS `date` 
UNION ALL
SELECT date_format(( curdate() - INTERVAL 10 MONTH ), '%Y-%m' ) AS `date` 
UNION ALL
SELECT date_format(( curdate() - INTERVAL 9 MONTH ), '%Y-%m' ) AS `date` 
UNION ALL
SELECT date_format(( curdate() - INTERVAL 8 MONTH ), '%Y-%m' ) AS `date` 
UNION ALL
SELECT date_format(( curdate() - INTERVAL 7 MONTH ), '%Y-%m' ) AS `date` 
UNION ALL
SELECT date_format(( curdate() - INTERVAL 6 MONTH ), '%Y-%m' ) AS `date` 
UNION ALL
SELECT date_format(( curdate() - INTERVAL 5 MONTH ), '%Y-%m' ) AS `date` 
UNION ALL
SELECT date_format(( curdate() - INTERVAL 4 MONTH ), '%Y-%m' ) AS `date` 
UNION ALL
SELECT date_format(( curdate() - INTERVAL 3 MONTH ), '%Y-%m' ) AS `date` 
UNION ALL
SELECT date_format(( curdate() - INTERVAL 2 MONTH ), '%Y-%m' ) AS `date` 
UNION ALL
SELECT date_format(( curdate() - INTERVAL 1 MONTH ), '%Y-%m' ) AS `date`

查询视图

SELECT * FROM year_month_view ORDER BY date