我正在尝试根据列的值“值”及其在所有行中该列总和的百分比,按比例向表的每一行分配数量。例如

AmounttoDistribute = 200
CurrentTotal = 100
Row 1 value = 25
Row 2 Value = 50
Row 3 Value = 15
Row 4 Value = 10


我想把它做成

Row 1 will receive 50
Row 2 will receive 100
Row 3 will receive 30
Row 4 will receive 20


我已经尝试了几种不同的while循环,但是到目前为止,结果与我要实现的结果非常不同。我已经在我知道是正确的变量中获得了$ AmounttoDistribute和$ SumofValue。这是我尝试过的:

$val = mysqli_query($db, "SELECT Value FROM Table WHERE columnX = 1 AND columnY = 1");
$Result = mysqli_fetch_array($val);
$value = $Result['Value'];

while($Result = mysqli_fetch_array($val)){
mysqli_query($db, "UPDATE Table SET Value = Value + (Value / $SumofValue * $AmounttoDistribute) WHERE columnX = 1 AND columnY = 1");


我还尝试了while循环,下面我已经有一个AmounttoDistribute变量除以SumofValue($ AmountPerEach1):

$val = mysqli_query($db, "SELECT Value FROM Table WHERE columnX = 1 AND columnY = 1");
$Result = mysqli_fetch_array($val);
$value = $Result['Value'];

while($Result = mysqli_fetch_array($val)){
mysqli_query($db, "UPDATE Table SET Value = Value + (Value * $AmountPerEach1) WHERE columnX = 1 AND columnY = 1");


谢谢你的帮助

最佳答案

如果您知道AmountToDistribute和CurrentTotal,则可以通过一个简单的SQL语句执行此操作。

基本上:

UPDATE table SET column = column + ((AmountToDistribute/CurrentTotal)*column);


专门针对这种情况:

UPDATE Table SET Value = Value + 2*Value;

关于php - 根据该行中某列的值,按比例将数字按比例分配给每一行。MySQL PHP,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43495324/

10-11 14:27