现有1张数据表3,是每个店铺的每个商品按月汇总的销量数据。现在需要将其转化为表4的格式(即将表3的每行数据拆分成5行)
数据表3 |
|
|
|
|
|
|
原始数据 |
|
|
|
|
|
|
店铺 | 商品 | 201801 | 201802 | 201803 | 201804 | 201805 |
B01 | A01 | 100 | 150 | 400 | 300 | 80 |
B01 | A02 | 105 | 135 | 165 | 195 | 225 |
B01 | A03 | 110 | 140 | 170 | 200 | 230 |
B01 | A06 | 125 | 155 | 185 | 215 | 245 |
B02 | A01 | 130 | 160 | 190 | 220 | 250 |
B02 | A02 | 135 | 165 | 195 | 225 | 255 |
|
|
|
|
|
|
|
数据表4 |
|
|
|
|
|
|
转化后的数据 |
|
|
|
|
| |
店铺 | 商品 | 月份 | 销量 |
|
|
|
B01 | A01 | 201801 | 100 |
|
|
|
B01 | A01 | 201802 | 150 |
|
|
|
B01 | A01 | 201803 | 400 |
|
|
|
B01 | A01 | 201804 | 300 |
|
|
|
B01 | A01 | 201805 | 80 |
|
|
|
B01 | A02 | 201801 | 105 |
|
|
|
B01 | A02 | 201802 | 135 |
|
|
|
B01 | A02 | 201803 | 165 |
|
|
|
B01 | A02 | 201804 | 195 |
|
|
|
B01 | A02 | 201805 | 225 |
|
|
|
…… | …… | …… | …… |
|
|
|
请写用sql 语句写一段程序,自动实现如上所需的数据转换。请将答案写在背面。
CREATE DEFINER=`root`@`localhost` PROCEDURE `two`(IN tab1 varchar(30),IN tab2 varchar(30))
BEGIN
/*店铺*/
DECLARE variable_code VARCHAR(50);
/*商品*/
DECLARE variable_commodity VARCHAR(50);
/*月份,如增加或删除月份*/
DECLARE jan int;
DECLARE feb int;
DECLARE mar int;
DECLARE apr int;
DECLARE may int;
declare i int;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT * FROM test_view ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
/* 将查到的数据存入视图 */
DROP VIEW IF EXISTS test_view;
SET @sqlstr = "CREATE VIEW test_view as ";
SET @sqlstr = CONCAT(@sqlstr,"SELECT * FROM ",tab1);
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
/* 创建表 */
SET @sqlstr = CONCAT("create table if not exists ",tab2," (
`code` varchar(255) DEFAULT NULL,
`commodity` varchar(255) DEFAULT NULL,
`month` varchar(255) DEFAULT NULL,
`number` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
open cur;
REPEAT
/*将查询出来的数据存入变量中,如果改变月份则需更改变量的个数即可*/
FETCH NEXT FROM cur INTO variable_code,variable_commodity,jan,feb,mar,apr,may;
/*例如7个月*/
/*FETCH NEXT FROM cur INTO variable_code,variable_commodity,jan,feb,mar,apr,may,jun,jul;*/
if not done then
/*插入五个月的数据。如果月份变化,增加sql语句即可*/
SET @sqlstr = CONCAT("INSERT INTO ",tab2," (code,commodity,month,number) VALUES
('",variable_code,"','",variable_commodity,"','201801',",jan,"),
('",variable_code,"','",variable_commodity,"','201802',",feb,"),
('",variable_code,"','",variable_commodity,"','201803',",mar,"),
('",variable_code,"','",variable_commodity,"','201804',",apr,"),
('",variable_code,"','",variable_commodity,"','201805',",may,")
");
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
END IF ;
UNTIL done END REPEAT;
DEALLOCATE PREPARE stmt;
CLOSE cur;
END