mysql - 循环存储过程中的列

Question

我有这张桌子：

Leaving   Month      HeadCount  Month_number    Attrition_Rate  
--------------------------------------------------------------
   1      2015-01-01    91          1           0.131868132
   2      2015-02-01    92          2           0.196721311 
   0      2015-03-01    90          3           0.132596685 
   1      2015-04-01    90          4           0.132596685 
   1      2015-06-01    93          6           0.108695652 
   2      2015-07-01    94          7           0.129729730 

我使用这个存储过程来更新损耗率：

PROCEDURE `CAL_ARF`()
BEGIN
    SET @cLeavig := 0;
    SET @cHeadCount := 0;
    SET @cHeadCount2 := 0;

    UPDATE `tabletest2`
    SET Attrition_Rate = CASE Month_number
                             WHEN 1 THEN (12 / Month_number) * ((@cLeaving := 0 + Leaving) / ((@cHeadCount := 0 + HeadCount) / Month_number)) 
                             ELSE (12 / Month_number) * ((@cLeaving := @cLeaving + Leaving) / ((@cHeadCount2 := @cHeadCount + HeadCount) / 2)) 
                         END
    ORDER by month;

如果我添加了一个额外的列，Dep_code并且我想为每个部门进行相同的计算，我应该在我的存储过程中添加什么代码？

例如：

Leaving Month   HeadCount   Mgmt_code   Month_number    Attrition_Rate  
1   2020-12-05  1   DIT04   12      
1   2020-12-05  66  DSH18   12      
1   2020-12-05  27  DSI02   12      
1   2020-12-05  76  DSY50   12      
2   2020-12-05  39  DSY53   12      
0   2020-12-05  1   DFB01   12      
0   2020-12-05  11  DOC10   12      

标签： mysqlstored-procedures