php - 对 MySQL 结果进行编号

Question

使用以下 MySQL 查询:

    SELECT  SUM(vg.count) AS sum, vg.code, lh.luchthavencode
    FROM 
    (
    SELECT COUNT( * ) AS COUNT, vg.vertrekluchthaven AS code
    FROM tbl_vluchtgegevens vg
    WHERE vg.vertrekdatum2 <= NOW( ) 
    GROUP BY vg.vertrekluchthaven

    UNION  

        SELECT COUNT(*) AS COUNT, vg.aankomstluchthaven AS code
        FROM tbl_vluchtgegevens vg  
        WHERE vg.vertrekdatum2 <=NOW()
        GROUP BY vg.aankomstluchthaven
    ) vg
    INNER JOIN tbl_luchthaven lh
    ON  lh.luchthavenID = vg.code
    GROUP BY vg.code
    ORDER BY sum DESC
    LIMIT 10

我试图弄清楚如何对结果进行编号，以便当我在 PHP 页面上显示它们时，更容易理解结果。

例如，目前我的结果将显示如下:

Sum   |   Code  |   Luchthavencode
41        121             MSP
26        91              AMS
9         246             ORD
8         116             DTW
6         169             LHR
6         142             IAH
6         346             SYR
5         149             JFK
5         13              AKL
5         417             FRA 

我想要的是这样的列表:

No. |   Sum   |   Code  |   Luchthavencode
1       41        121             MSP
2       26        91              AMS
3       9         246             ORD
4       8         116             DTW
5       6         169             LHR
6       6         142             IAH
7       6         346             SYR
8       5         149             JFK
9       5         13              AKL
10      5         417             FRA 

最好是通过 MySQL 查询还是通过 PHP 输出来实现？

我尝试将其添加到我的 SELECT (@row_number:=@row_number + 1) AS num 中，但它对它们进行编号:45, 4, 47, 18, 34, 24, 56, 27, 2, 63

编辑以包含输出 PHP:

while($row = mysqli_fetch_assoc($result)) {

    echo"  <table width='100px' border='0' cellpadding='1' cellspacing='1'>";
    echo "  <tbody>";
    echo "    <tr>";
    echo "      <td width='60px'><strong>&nbsp; &nbsp;".$row['luchthavencode']."</strong></td>";
    echo "      <td width='30px'><strong>&nbsp; &nbsp;".$row['sum']."</strong></td>";
    echo "    </tr>";
    echo "  </tbody>";
    echo "</table>";    
}

Answer 1

我假设您想要一个包含此数据的表，因此我修复了我认为表构建中的错误并添加了计数器

echo "<table width='100px' border='0' cellpadding='1' cellspacing='1'>";
echo "<tbody>";

$line = 1;

while($row = mysqli_fetch_assoc($result)) {

    echo "<tr>";
    echo "<td>$line</td>";
    echo "<td width='60px'><strong>&nbsp; &nbsp;".$row['luchthavencode']."</strong></td>";
    echo "<td width='30px'><strong>&nbsp; &nbsp;".$row['sum']."</strong></td>";
    echo "</tr>";

    $line++;
}

echo "</tbody>";
echo "</table>";