使用PHP访问SQL别名
使用PHP访问SQL别名
提问于 2020-10-12 22:14:15
这段代码可以满足我在phpMyAdmin服务器中的需求：
SET @row_number = 0;
SELECT salesperson, units, profit, (@row_number:=@row_number + 1) AS position
FROM sales AS t ORDER BY profit DESC;当我将这个代码传输到php时，问题就出现了：
<?php
$sql = "SELECT salesperson, units, profit, (@row_number:=@row_number + 1) AS position\n"
. "FROM sales AS t ORDER BY profit DESC";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if ($resultCheck > 0){
while ($row = mysqli_fetch_assoc($result))
echo $row['salesperson'] . " " . $row['units'] . " " . $row['profit'] .$row['position'] . "<br>";
}它输出前三行，但不输出第四行。使用SQL创建的别名。
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-10-12 22:35:00
首先，您选择的并不是调试输出的最佳方法。直接限定$row['position'] (通过将其传递到echo中)隐藏了许多有用的信息，否则您将能够找到这些信息。
其次，第二个代码片段中的查询遗漏了对该变量的实际设置，如下所示：
$sql = <<<QUERY
SELECT salesperson, units, profit,
(@row_number:=@row_number + 1) AS position
FROM sales AS t, (SELECT @row_number := 0) AS r
ORDER BY profit DESC
QUERY;
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if ($resultCheck > 0){
while ($row = mysqli_fetch_assoc($result)) {
echo "$row[salesperson] $row[units] $row[profit] $row[position]<br>";
}
}Stack Overflow用户
发布于 2020-10-13 00:14:02
此外，在最新版本的MySQL (8.0+)中，最终支持window functions，这避免了使用@会话变量。为此，您还可以使用PARTITION BY子句按group添加行号：
SELECT salesperson
, units
, profit
, ROW_NUMBER() OVER(ORDER BY profit DESC) AS `overall_position`
, ROW_NUMBER() OVER(PARTITION BY salesperson
ORDER BY profit DESC) AS `salesperson_position`
FROM sales
ORDER BY profit DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64319600
复制相关文章
相似问题
腾讯云开发者
