MySQL＆PHP foreach中的行数总和

Question

我试图在一行中获得锻炼总数，然后按总和排序所有行，以获取团队的位置，并按正确的顺序列出。我正在试图弄清楚这一点，我现在正在以自己的逻辑迷失自己。我了解MYSQL总和功能，但似乎无法看到我可以如何使用它来帮助我在这种情况下。

因此，像这样：

这里是我当前的表模式：

CREATE TABLE workouts 
(
team_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
username VarChar(255) NOT NULL, 
team_name VarChar(50) NOT NULL, 
week1 INT NOT NULL, 
week2 INT NOT NULL, 
week3 INT NOT NULL, 
week4 INT NOT NULL, 
week5 INT NOT NULL, 
week6 INT NOT NULL, 
week7 INT NOT NULL, 
week8 INT NOT NULL, 
week9 INT NOT NULL, 
week10 INT NOT NULL, 
week11 INT NOT NULL, 
week12 INT NOT NULL 
) engine=innodb; 

，这里是到目前为止我显示：

<?php 
$count = 0; 
$statement = $db->query('SELECT * FROM workouts'); 
foreach($statement as $row): 
?> 
<tr> 
<td><?php $count++; ?></td> 
<td><?php $row['team_name']; ?></td> 
<td><?php $row['week1']; ?></td> 
<td><?php $row['week2']; ?></td> 
<td><?php $row['week3']; ?></td> 
<td><?php $row['week4']; ?></td> 
<td><?php $row['week5']; ?></td> 
<td><?php $row['week6']; ?></td> 
<td><?php $row['week7']; ?></td> 
<td><?php $row['week8']; ?></td> 
<td><?php $row['week9']; ?></td> 
<td><?php $row['week10']; ?></td> 
<td><?php $row['week11']; ?></td> 
<td><?php $row['week12']; ?></td> 
<td><?php ?></td> 
</tr> 
<?php endforeach; ?> 

Answer 1

那么最简单的答案基于：除了利用的Sum()，其目的是增加了一列下来整个分组序列，只是添加了列，并为其指定别名“合计”，像这样的对你已经拥有的总结行是这样的：

<?php 
$count = 0; 
$statement = $db->query('SELECT * FROM workouts'); 
foreach($statement as $row): 
?> 
<tr> 
<td><?php echo $count++; ?></td> 
<td><?php echo $row['team_name']; ?></td> 
<td><?php echo $row['week1']; ?></td> 
<td><?php echo $row['week2']; ?></td> 
<td><?php echo $row['week3']; ?></td> 
<td><?php echo $row['week4']; ?></td> 
<td><?php echo $row['week5']; ?></td> 
<td><?php echo $row['week6']; ?></td> 
<td><?php echo $row['week7']; ?></td> 
<td><?php echo $row['week8']; ?></td> 
<td><?php echo $row['week9']; ?></td> 
<td><?php echo $row['week10']; ?></td> 
<td><?php echo $row['week11']; ?></td> 
<td><?php echo $row['week12']; ?></td> 
<td><?php echo $row['week1'] + $row['week2'] + $row['week3'] + $row['week4'] + $row['week5'] + $row['week6'] + $row['week7'] + $row['week8'] + $row['week9'] + $row['week10'] + $row['week11'] + $row['week12']; ?></td> 
</tr> 
<?php endforeach; ?> 

但为了使用这个值来控制其中显示的行，你需要让MySQL为你做这项工作的顺序。

什么，你会想要做的是这样的：

<?php 

    $count = 0; 
    $statement = $db->query(' 
    SELECT *, week1 + week2 + week3 + week4 + week5 + week6 + week7 + week8 + week9 + week10 + week11 + week12 AS Total 
    FROM workouts 
    ORDER BY Total 
    '); 
    foreach ($statement as $row): 
?> 
<tr> 
<td><?php echo $count++; ?></td> 
<td><?php echo $row['team_name']; ?></td> 
<td><?php echo $row['week1']; ?></td> 
<td><?php echo $row['week2']; ?></td> 
<td><?php echo $row['week3']; ?></td> 
<td><?php echo $row['week4']; ?></td> 
<td><?php echo $row['week5']; ?></td> 
<td><?php echo $row['week6']; ?></td> 
<td><?php echo $row['week7']; ?></td> 
<td><?php echo $row['week8']; ?></td> 
<td><?php echo $row['week9']; ?></td> 
<td><?php echo $row['week10']; ?></td> 
<td><?php echo $row['week11']; ?></td> 
<td><?php echo $row['week12']; ?></td> 
<td><?php echo $row['Total']; ?></td> 
</tr> 
<?php endforeach; ?> 

Answer 2

我会做的部分的SQL，而不是PHP。

SELECT week1 + week2 + week3 + weekN Total FROM workouts ORDER BY Total 

Answer 3

这里是我的解决方案。

我将它们转储到一个关联数组中，并将训练总数作为关键字，而不仅仅是在处理它们时回显数据库值。然后你可以使用kso​​rt对它们进行排序。一旦完成，您可以使用foreach循环打印出数组。

我没有输入完整的数组函数，但它应该给你一个想法。

<?php 
$count = 0; 

$statement = $db->query('SELECT * FROM workouts'); 

$workouts = array(); 

while ($row = mysql_fetch_array($statement)) { 

    $total = 0; 

    for($i = 0;$i < 13; $i++) 
     $total = $total + $row['week'.$i]; 

    //Build the array containing each teams workouts 
    $workouts[$total] = array('team_name' => $row['team_name'],'week1' => $row['week1']); 
} 

ksort($workouts); 

$rowcount = 0; 

foreach ($workouts as $total => $team): ?> 
    <tr> 
    <td><?php echo $rowcount++; ?></td> 
    <td><?php echo $team['team_name']; ?></td> 
    <td><?php echo $team['week1']; ?></td> 
    <td><?php echo $team['week2']; ?></td> 
    <td><?php echo $team['week3']; ?></td> 
    <td><?php echo $team['week4']; ?></td> 
    <td><?php echo $team['week5']; ?></td> 
    <td><?php echo $team['week6']; ?></td> 
    <td><?php echo $team['week7']; ?></td> 
    <td><?php echo $team['week8']; ?></td> 
    <td><?php echo $team['week9']; ?></td> 
    <td><?php echo $team['week10']; ?></td> 
    <td><?php echo $team['week11']; ?></td> 
    <td><?php echo $team['week12']; ?></td> 
    <td><?php echo $total ?></td> 
    </tr> 
<?php endforeach ?> 

Answer 4

如果我理解正确的，你想要的队按顺序在每个小组的用户（也许他们自己的顺序）。

要获得团队订单，您需要加入。

with wo as (select wo.*, 
        (week1 + week2 + . . . weekn) as weektotal 
      from workouts 
      ) 
select wo.* 
from wo join 
    (select team_id, sum(weektotal) as weektotal 
     from wo 
     group by team_id 
    ) wot 
    on wo.team_id = wot.team_id 
order by wot.weektotal desc, wot.team_id, wo.weektotal desc