给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输出来实现的呢？

我尝试将其作为num添加到我的SELECT (@row_number:=@row_number + 1)中，但其编号如下: 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>";    

Answer 2

在查询开始时使用此代码来获取多个行

SELECT @rownum:=@rownum+1 AS rownum

Answer 3

如果使用变量有效，请尝试此检查是否有效：

SELECT
    @rowno := @rowno + 1 AS `No.`
    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
CROSS JOIN (SELECT @rowno := 0) rn
GROUP BY
    vg. CODE
ORDER BY
    sum DESC
LIMIT 10