使用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创建的别名。
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
复制相关文章
相似问题
腾讯云开发者
