MySQL存储过程实现，如何将数据拆分为7条

素颜马尾好姑娘i 2022-05-08 20:38 208阅读 0赞

现有一张数据表1，是某商品每个店铺每周的销量数据（用周日的日期代表其所在的周）。现在需要将此数据表转化为如表2的格式，即将每周的销量拆解为该周每天的平均销量数据（日平均销量=周销量/7）.

<table style="width:462.65pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> &nbsp;&nbsp;&nbsp;&nbsp; 数据表1 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:79pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:58.25pt;"> 数据表2 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:84pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> 原始数据 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:79pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td colspan="2" style="vertical-align:bottom;width:117.95pt;"> 计算处理后的数据 </td> 
 <td style="vertical-align:bottom;width:84pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#808080;width:64pt;"> 店铺代码 </td> 
 <td style="background-color:#808080;width:59.7pt;"> 周 </td> 
 <td style="background-color:#808080;width:79pt;"> 周销售数量 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#808080;width:58.25pt;"> 店铺代码 </td> 
 <td style="background-color:#808080;width:59.7pt;"> 日期 </td> 
 <td style="background-color:#808080;width:84pt;"> 日平均销售数量 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#00b050;width:64pt;"> A01 </td> 
 <td style="background-color:#00b050;width:59.7pt;"> 2018/1/7 </td> 
 <td style="background-color:#00b050;width:79pt;"> 200 </td> 
 <td style="vertical-align:bottom;width:30pt;"> </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b050;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b050;width:59.7pt;"> 2018/1/1 </td> 
 <td style="background-color:#00b050;width:84pt;"> 28.57 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#00b0f0;width:64pt;"> A01 </td> 
 <td style="background-color:#00b0f0;width:59.7pt;"> 2018/1/14 </td> 
 <td style="background-color:#00b0f0;width:79pt;"> 300 </td> 
 <td style="vertical-align:bottom;width:30pt;"> </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b050;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b050;width:59.7pt;"> 2018/1/2 </td> 
 <td style="background-color:#00b050;width:84pt;"> 28.57 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A01 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/1/21 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 400 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b050;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b050;width:59.7pt;"> 2018/1/3 </td> 
 <td style="background-color:#00b050;width:84pt;"> 28.57 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A01 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/1/28 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 280 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b050;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b050;width:59.7pt;"> 2018/1/4 </td> 
 <td style="background-color:#00b050;width:84pt;"> 28.57 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A01 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/2/4 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 350 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b050;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b050;width:59.7pt;"> 2018/1/5 </td> 
 <td style="background-color:#00b050;width:84pt;"> 28.57 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A01 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/2/11 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 420 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b050;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b050;width:59.7pt;"> 2018/1/6 </td> 
 <td style="background-color:#00b050;width:84pt;"> 28.57 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A01 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/2/18 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 490 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b050;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b050;width:59.7pt;"> 2018/1/7 </td> 
 <td style="background-color:#00b050;width:84pt;"> 28.57 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A01 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/2/25 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 560 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b0f0;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b0f0;width:59.7pt;"> 2018/1/8 </td> 
 <td style="background-color:#00b0f0;width:84pt;"> 42.86 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A02 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/1/7 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 200 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b0f0;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b0f0;width:59.7pt;"> 2018/1/9 </td> 
 <td style="background-color:#00b0f0;width:84pt;"> 42.86 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A02 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/1/14 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 300 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b0f0;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b0f0;width:59.7pt;"> 2018/1/10 </td> 
 <td style="background-color:#00b0f0;width:84pt;"> 42.86 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A02 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/1/21 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 400 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b0f0;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b0f0;width:59.7pt;"> 2018/1/11 </td> 
 <td style="background-color:#00b0f0;width:84pt;"> 42.86 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A02 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/1/28 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 280 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b0f0;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b0f0;width:59.7pt;"> 2018/1/12 </td> 
 <td style="background-color:#00b0f0;width:84pt;"> 42.86 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A02 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/2/4 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 350 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b0f0;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b0f0;width:59.7pt;"> 2018/1/13 </td> 
 <td style="background-color:#00b0f0;width:84pt;"> 42.86 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A02 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/2/11 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 420 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#00b0f0;width:58.25pt;"> A01 </td> 
 <td style="background-color:#00b0f0;width:59.7pt;"> 2018/1/14 </td> 
 <td style="background-color:#00b0f0;width:84pt;"> 42.86 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A02 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/2/18 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 490 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="background-color:#ffffff;width:58.25pt;"> …… </td> 
 <td style="background-color:#ffffff;width:59.7pt;"> …… </td> 
 <td style="background-color:#ffffff;width:84pt;"> …… </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:bottom;width:64pt;"> A02 </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> 2018/2/25 </td> 
 <td style="vertical-align:bottom;width:79pt;"> 560 </td> 
 <td style="vertical-align:bottom;width:30pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:28pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:58.25pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:59.7pt;"> &nbsp; </td> 
 <td style="vertical-align:bottom;width:84pt;"> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

请用sql 语句写一段代码，实现由数据表1到数据表2的自动转换功能。请将答案写在背面。

![70][]

CREATE DEFINER=`root`@`localhost` PROCEDURE `one`(IN tab1 varchar(30),IN tab2 varchar(30))
    BEGIN
    	 /* 店铺代码 */
    	DECLARE variable_code VARCHAR(50);
    	/* 周 */
    	DECLARE variable_wek date;
    	/* 周销售量 */
    	DECLARE variable_num DOUBLE;
    	/* 一周7天 */
    	declare i int; 
    	/* 游标是否读取完毕，完毕后变为1 */
    	DECLARE done INT DEFAULT 0;
    	/* 声明游标 */
    	DECLARE cur CURSOR for SELECT code,week,num/7 as num FROM test_view;
    	DECLARE CONTINUE HANDLER FOR  SQLSTATE '02000' SET done = 1;		
    	/* 将查到的数据存入视图 */
    	DROP VIEW IF EXISTS test_view;  
    	SET @sqlstr = "CREATE VIEW test_view as ";  
      SET @sqlstr = CONCAT(@sqlstr,"SELECT code,week,num/7 as num FROM ",tab1);  
      PREPARE stmt FROM @sqlstr;  
      EXECUTE stmt;		
    	/* 创建表 */
    	DROP TABLE IF EXISTS tab2;
    	SET @sqlstr = CONCAT("create table if not exists ",tab2," (
    		`code` varchar(255) DEFAULT NULL,
    		`wek` date DEFAULT NULL,
    		`num` double(11,2) DEFAULT NULL
    	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");  
    	PREPARE stmt FROM @sqlstr;  
    	EXECUTE stmt;  
    	open cur;
    	/* 循环插入数据 */
    	REPEAT
    		FETCH NEXT FROM cur INTO variable_code,variable_wek,variable_num;
    		if not done then		
    			set i=7; 
    			while i > 0 do
    				SET @sqlstr = CONCAT("INSERT INTO ",tab2,"(code,wek,num)VALUES('",variable_code,"','",DATE_ADD(variable_wek,INTERVAL -i DAY),"','",variable_num,"')");  
    				PREPARE stmt FROM @sqlstr;  
    				EXECUTE stmt;  
    			 set i=i-1;
    			end while;
        END IF ;
    	UNTIL done END REPEAT;
    CLOSE cur;
    END

[70]: /images/20220509/5e69578dfe794878be2ce42de115ddf8.png