php - 显示总人口变化并按上次人口变化时间排序

Question

我正在尝试使用数据库中的数据创建一个表，并使用 php 代码来查看每个城镇的人口并将它们全部加起来以获得每天的告知人口，然后获取告知人口pop 并查找上次 pop 更改的时间，并输出该数据并按上次 pop 更改的时间排序。任何帮助都会很棒

现在显示的内容

+-----------+------------+---------------+-----------------------+
| Last_time_pop_changed  | Player_name   |total_population_change|
+-----------+------------+---------------+-----------------------+
| 2016-02-07 00:03:46    | .Mjölnir.     |                 383846|
| 2016-02-08 00:03:25    | .Mjölnir.     |                 384309|
| 2016-02-09 00:03:30    | .Mjölnir.     |                 384347|
| 2016-02-10 00:04:09    | .Mjölnir.     |                 384374|
| 2016-02-11 00:03:29    | .Mjölnir.     |                 385163|
| 2016-02-12 00:02:51    | .Mjölnir.     |                 385173|
| 2016-02-13 00:02:59    | .Mjölnir.     |                 385190|
| 2016-02-14 00:03:12    | .Mjölnir.     |                 385904|
| 2016-02-15 00:04:11    | .Mjölnir.     |                 386217|
| 2016-02-16 00:03:58    | .Mjölnir.     |                 386254|
| 2016-02-17 00:03:39    | .Mjölnir.     |                 386295|
| 2016-02-07 00:03:46    |  Rebellions   |                  23084|
| 2016-02-08 00:03:25    |  Rebellions   |                  22382|
| 2016-02-09 00:03:30    |  Rebellions   |                  22382|
| 2016-02-10 00:04:09    |  Rebellions   |                  22382|
| 2016-02-11 00:03:29    |  Rebellions   |                  22382|
| 2016-02-12 00:02:51    |  Rebellions   |                  22382|
| 2016-02-13 00:02:59    |  Rebellions   |                  22382|
| 2016-02-14 00:03:12    |  Rebellions   |                  22382|
| 2016-02-15 00:04:11    |  Rebellions   |                  22382|
| 2016-02-16 00:03:58    |  Rebellions   |                  22382|
| 2016-02-17 00:03:39    |  Rebellions   |                  22382|   
+-----------+------------+---------------+-----------------------+

我希望它显示什么

+-----------+------------+---------------+-----------------------+
| Last_time_pop_changed  | Player_name   |total_population_change|
+-----------+------------+---------------+-----------------------+
| 2016-02-08 00:03:25    |  Rebellions   |                   -702|
| 2016-02-16 00:03:58    | .Mjölnir.     |                     41|
+-----------+------------+---------------+-----------------------+

PHP代码

$sql="SELECT players.*, town_deltas.*, town_deltas.town_id, sum( town_deltas.population ) as total_population  FROM `town_deltas` LEFT JOIN players ON town_deltas.owner_id = players.player_id  GROUP BY owner_id, data_timestamp ORDER BY `town_deltas`.`town_id` ASC, `town_deltas`.`data_timestamp` ASC";
$result = mysqli_query($con,$sql);
echo "<table>
<tr>

<th>Last_time_pop_changed</th>

<th>Player_name</th>


<td>total_population_change</td>

</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";


echo "<td>" . $row['data_timestamp'] . "</td>";

echo "<td>" . $row['Player_name'] . "</td>";


echo "<td>" . $row['total_population'] . "</td>";


echo "</tr>";
}

城镇三角洲

                primary_key :id
                DateTime :happened_at
                Integer :town_id, :index => true
                Integer :owner_id, :index => true
                String :name, :index => true
                Integer :population
                TrueClass :is_capital
                TrueClass :is_alliance_capital

玩家表

            Integer :player_id, :primary_key => true
            foreign_key :race_id, :races
            Integer :alliance_id, :null => true
            Integer :alliance_role_id, :null => true
            String :Player_name, :index => true

Answer 1

我能想到的最好的方法是一次循环一个名字

    select data_timestamp,sum(total_population_change) from (select * from town_deltas where Player_name  = name limit 2) as t;