行排名查询在MySQL中正常运行，在PHP中执行报错求助

Hey, I see exactly what's going on here!

Your SQL query works perfectly in MySQL because it supports running multiple statements separated by semicolons, but PHP's mysqli->query() method doesn't allow executing multiple SQL statements in a single call by default—that's why you're getting that syntax error pointing to the second SELECT statement.

Let's fix this with two solid solutions, starting with the most secure and recommended one:

Solution 1: Rewrite as a single-statement query (best practice) #

You can initialize the @row_number variable directly within the query using a cross join, eliminating the need for two separate statements. This works with regular mysqli->query() and avoids any multi-statement security risks:

$query = "SELECT @row_number := @row_number + 1 AS `row_number`, TotalRevenue, CompanyID 
          FROM ( 
              SELECT CompanyID, SUM(`Sales_Amt`) AS TotalRevenue 
              FROM Sales 
              GROUP BY CompanyID 
              HAVING TotalRevenue > 0
          ) t, (SELECT @row_number := 0) r 
          ORDER BY TotalRevenue DESC";

if (!$result = $mysqli->query($query)) {
    print_r($mysqli->error);
} else {
    // Process your results here
    while ($row = $result->fetch_assoc()) {
        echo "Rank: {$row['row_number']} | Revenue: {$row['TotalRevenue']} | Company: {$row['CompanyID']}<br>";
    }
}

I also swapped the WHERE TotalRevenue > 0 for HAVING TotalRevenue > 0 in the subquery—this is more efficient since it filters aggregated results directly instead of filtering after the subquery runs.

Solution 2: Use mysqli_multi_query() for multi-statement queries #

If you want to keep your original multi-statement structure, you'll need to use mysqli_multi_query() instead of the standard query() method. Just be cautious: this approach carries a higher SQL injection risk if your query includes user input, so only use it if you're 100% sure the query is safe.

$query = "SET @row_number := 0; SELECT @row_number := @row_number + 1 AS `row_number`, TotalRevenue, CompanyID FROM ( SELECT CompanyID, SUM(`Sales_Amt`) AS TotalRevenue FROM Sales GROUP BY CompanyID ) t WHERE TotalRevenue > 0 ORDER BY TotalRevenue DESC";

if ($mysqli->multi_query($query)) {
    // Skip the result from the SET statement (we don't need it)
    $mysqli->next_result();
    // Grab the actual SELECT result set
    if ($result = $mysqli->store_result()) {
        while ($row = $result->fetch_assoc()) {
            // Process your results
            print_r($row);
        }
        $result->free();
    }
} else {
    print_r($mysqli->error);
}

All the tweaks you tried earlier (backticks, := instead of =, checking whitespace) were actually correct—they just weren't addressing the core issue of multi-statement execution in mysqli.

内容的提问来源于stack exchange，提问作者Heather Stewart