MySQL 如何将 ROW INDEX 作为列添加到 SQL SELECT 查询中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13550750/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add ROW INDEX as a column to SQL SELECT query?
提问by Yarin Gold
Assume I've SQL query like this:
假设我有这样的 SQL 查询：
SELECT id, name, index(not a real column) FROM users ORDER BY rating DESC
I want to add column to selected columns that will represent the index of the record.
我想将列添加到将表示记录索引的选定列。
Example:
例子：
id name rating
1 a 4
2 b 2
3 c 8
4 d 5
For this table I want to get:
对于这张桌子，我想得到：
id name rating index
3 c 8 1
4 d 5 2
1 a 4 3
2 b 2 4
回答by bonCodigo
Try the following to get the row_index:
尝试以下操作以获取 row_index：
set @row_num = 0;
SELECT id,name,rating, @row_num := @row_num + 1 as row_index FROM users
ORDER BY rating desc;
回答by nvlass
Something like
set @cnt=0; select *, @cnt:=@cnt+1 from users order by rating desc;
就像是
set @cnt=0; select *, @cnt:=@cnt+1 from users order by rating desc;
Should do the job (You'll need to set @cnt before each query though)
应该完成这项工作（尽管您需要在每次查询之前设置 @cnt）
回答by Nguyen Tan Dat
@bonCodigo's answer is correct when you try in php admin or in sql code. But if you want to call a query in php, you should declare it as follows:
当您在 php admin 或 sql 代码中尝试时，@bonCodigo 的答案是正确的。但是如果你想在php中调用一个查询，你应该如下声明：
$sql_query = "SELECT (@row_number:=@row_number + 1) AS 'row_index', id, name, rating FROM (SELECT @row_number:=0) AS temp, users"
$this->db->query ( $sql_query )->result ();
(This piece of code is for Code igniter framework)
（这段代码是针对代码点火器框架的）
UPDATED:
更新：
However, it won't work when using JOINtogether. In this case, you need to read data as usual then add a column index by yourself, you can take an idea from this code:
但是，JOIN一起使用时不起作用。在这种情况下，你需要像往常一样读取数据然后自己添加一个列索引，你可以从这段代码中得到一个想法：
$list = $this->db->query ( $sql_query )->result ();
for($i = 0; $i < count($list); $i++ ){
$list[$i]->no = $i + 1;
}
(This piece of code is for Code igniter framework)
（这段代码是针对代码点火器框架的）
回答by caram
It's now builtin in MySQL 8.0 and MariaDB 10.2:
它现在内置在 MySQL 8.0 和 MariaDB 10.2 中：
SELECT
id, name, rating,
ROW_NUMBER(ORDER BY rating DESC) AS index
FROM users ORDER BY rating DESC
回答by Roman Gudkov
If you don't have a column to order by, you can still use ROW_NUMBER():
如果您没有要订购的列，您仍然可以使用ROW_NUMBER()：
SELECT
id, name, rating,
ROW_NUMBER(ORDER BY (select 1)) AS index
FROM users
