mysql 生成时间序列数据 - 存储过程

use `test`;CREATE table test.test1 as
SELECT state, id, `规格条码`,
`色号条码`,
`货号`,
`在售平台`,
`平台售价`,
DATE_ADD('1900-01-01', Interval data1.`上架时间` day) as `上架时间`,
`下架时间`,
`操作员`
FROM data1;CREATE table test.test2 as
SELECT state, id,
DATE_ADD('1900-01-01', Interval `时间` day) as `时间`,
`在售平台`,
`规格条码`,
`销量`,
`销售额`,
`撤销标志`
FROM data2;

USE `test`;
DROP TABLE IF EXISTS tmptb;
CREATE TEMPORARY TABLE tmptb (
id INT UNSIGNED AUTO_INCREMENT,
date DATE NOT NULL,
shop VARCHAR(20) NOT NULL DEFAULT 0,
sales INT UNSIGNED DEFAULT 0,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER //
DROP PROCEDURE IF EXISTS DayRangeProc//
CREATE PROCEDURE DayRangeProc (IN start_date DATE,IN end_date DATE)
BEGIN
DECLARE i, range_day INT;
SET i = 0;
SET range_day = (SELECT DATEDIFF(end_date, start_date));
WHILE i <= range_day DO
INSERT INTO tmptb(date) VALUES (ADDDATE(start_date, i));
-- SET @sqlcmd = CONCAT('INSERT INTO ', tmptb, ' (date) VALUES (', temp, ')');
-- PREPARE stmt FROM @sqlcmd;
-- EXECUTE stmt;
-- DEALLOCATE PREPARE stmt;
SET i = i + 1;
ENDWHILE;
END;
//DELIMITER ;
CALL DayRangeProc ('2010-09-01', '2010-09-10');
SELECT * FROMtmptb;

USE `test`;
DROP TABLE IF EXISTS result;
CREATE TABLE result (
-- 保存结果数据
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date DATE NOT NULL,
product_id VARCHAR(20) NOT NULL,
shop VARCHAR(20) NOT NULL DEFAULT 0,
price FLOAT NOT NULL,
sales INT(8) DEFAULT 0,
amount DOUBLE DEFAULT 0,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS shop_name_tb;
CREATE TEMPORARY TABLE shop_name_tb AS (SELECT DISTINCT`在售平台` AS NAME FROMtest2); -- 保存平台，类似于数组操作DELIMITER //
DROP PROCEDURE IF EXISTS DayRangeProc//
CREATE PROCEDURE DayRangeProc()
BEGIN
DECLARE i, j, t INT;
DECLARE range_day INT;
DECLARE shop_num, prod_num INT;
DECLARE start_date, end_date DATE;
DECLARE prod_id, shop_name VARCHAR(20);
DECLARE price FLOAT;SET i = 0;
SET j = 0;
SET t = 0;
SET shop_num = (SELECT COUNT(*) FROM shop_name_tb);
SET prod_num = (SELECT COUNT(*) FROM test1);
SET end_date = (SELECT MAX(`时间`) FROM test2 ); -- 由于下架时间均为空，假设都在销
-- 产品循环
WHILE i <= prod_num DO
SET prod_id = (SELECT `规格条码` FROM test1 LIMIT i,1); -- 第i个商品名称
SET start_date = (SELECT `上架时间` FROM test1 WHERE `规格条码` = prod_id); -- 第i个商品的上架时间
SET range_day = (SELECT DATEDIFF(end_date, start_date)); -- 第i个商品累计销售天数，以便插入相应长度的数据-- 平台循环
WHILE j <= shop_num DO
SET shop_name = (SELECT name FROM shop_name_tb LIMIT j,1); -- 店铺名称
SET price = (SELECT `平台售价` FROM test1 WHERE `规格条码` = prod_id); -- 第i个商品售价，假设不同平台售价相同-- 时间循环
WHILE t <= range_day DO
INSERT INTO result(date,product_id, shop, price)
VALUES (ADDDATE(start_date, t), prod_id, shop_name, price); -- sales, amount
SET t = t + 1;
ENDWHILE;SET j = j + 1;
ENDWHILE;
SET i = i + 1;
ENDWHILE;
END;
//DELIMITER ;
CALL DayRangeProc (); -- 查询数据
SELECT
result.id,
result.date,
result.product_id,
result.shop,
result.price,
IF(ISNULL(test2.`销量`), result.sales, test2.`销量`) AS sales,
IF(ISNULL(test2.`销售额`), result.amount, test2.`销售额`) AS amount
FROMresult LEFT JOIN test2
ON result.date = test2.`时间`
AND result.shop = test2.`在售平台`
AND result.product_id = test2.`规格条码`;

USE test;
DROP TABLE IF EXISTS TEMP;
CREATE TABLE TEMP (
ID INT (8) NOT NULL AUTO_INCREMENT,
number VARCHAR(20) NOT NULL,
PRIMARY KEY (ID)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '测试';DELIMITER $$
DROP PROCEDURE IF EXISTS `Pr_Rand_insert`$$
CREATE PROCEDURE `Pr_Rand_insert` (IN insert_string VARCHAR (10000)) -- 定义输入
BEGIN
DECLARE I INT (8) DEFAULT 1 ;
DECLARE J INT (8) DEFAULT 0 ;
SET J = CHAR_LENGTH(insert_string) - CHAR_LENGTH(REPLACE (insert_string, ',', '')) + 1; -- 计算共有多少位为",",则再加上1就表示共有多少个数值需要插入
WHILE (I <= J) DO
INSERT INTO TEMP(number) VALUES(SUBSTRING_INDEX(SUBSTRING_INDEX(insert_string, ',', I), ',', - 1)) ; -- 用到了substring_index()函数
SET I = I + 1 ;
END WHILE ;
-- SELECTCONCAT('共插入了', J, '个值,请确认');
END$$
DELIMITER ;CALL Pr_Rand_insert ('231,24,1114,151,7831241,9134,989');
SELECT * FROMTEMP;