mysql - Mysqli排名语法

Question

我的mysqli有问题。当我尝试在php中使用它时出现错误，但是当我在某些database application中执行查询时，它运行良好。它给我错误

SET @rank=0; SELECT (@rank := @rank+1)

我可以在 mysql yog中正确执行它。有没有替代方法，我如何使它工作？

这是代码：

function get_rank($branch,$cat){
    global $connection;
    $result = array();
    $rank = 0;
    if ($statement = $connection->prepare("SET @rank=0; SELECT (@rank := @rank+1) AS rank, a.branch_code_id, SUM(b.amount), c.category FROM sales_add_h AS a INNER JOIN sales_add_i AS b ON a.id = b.sales_h_id INNER JOIN control_panel_item_create AS c ON b.item_code_id = c.id WHERE a.branch_code_id = ? AND c.category = ? GROUP BY c.category, a.branch_code_id, b.amount ORDER BY SUM(b.amount) DESC")) {        
       $statement->bind_param("is",$branch,$cat); 
       $statement->execute();
       $statement->bind_result($a,$b,$c,$d);
        while ($row = $statement->fetch()) {
            array_push($result,array($a,$b,$c,$d));
        }     
        $statement->close();
    } else  {
        printf("Errormessage: %s\n", $connection->error);
        echo " error in SQL Statement.";
    }
    return $result;
}

Answer 1

您正在执行2个查询您的代码，并且该查询不适用于该PHP函数。但是您可以将set集成到select

SELECT (@rank := @rank+1) AS rank, a.branch_code_id, SUM(b.amount), c.category 
FROM sales_add_h AS a, 
     (select @rank := 0) r 
INNER JOIN sales_add_i AS b ON a.id = b.sales_h_id 
INNER JOIN control_panel_item_create AS c ON b.item_code_id = c.id
WHERE a.branch_code_id = ? 
AND c.category = ? 
GROUP BY c.category, a.branch_code_id, b.amount 
ORDER BY SUM(b.amount) DESC