MySQL：选择语句中的自动增量临时列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15930514/
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
MySQL: Auto increment temporary column in select statement
提问by Sg1456
How do I create and auto increment a temporary column in my select statement with MySQL?
如何在 MySQL 的 select 语句中创建和自动增加一个临时列？
Here is what I have so far:
这是我到目前为止所拥有的：
SET @cnt = 0;
SELECT
(@cnt =@cnt + 1) AS rowNumber,
rowID
FROM myTable
WHERE CategoryID = 1
Which returns:
返回：
+++++++++++++++++++++
+ rowNumber | rowID +
+++++++++++++++++++++
+ (NULL) | 1 +
+ (NULL) | 25 +
+ (NULL) | 33 +
+ (NULL) | 150 +
+ (NULL) | 219 +
+++++++++++++++++++++
But I need:
但是我需要：
+++++++++++++++++++++
+ rowNumber | rowID +
+++++++++++++++++++++
+ 1 | 1 +
+ 2 | 25 +
+ 3 | 33 +
+ 4 | 150 +
+ ... | ... +
+++++++++++++++++++++
回答by Kermit
This will give you a consecutiverow number with 3.
这将为您提供一个连续的行号3。
SELECT
(@cnt := @cnt + 1) AS rowNumber,
t.rowID
FROM myTable AS t
CROSS JOIN (SELECT @cnt := 0) AS dummy
WHERE t.CategoryID = 1
ORDER BY t.rowID ;
Result
结果
| ROWNUMBER | ROWID | --------------------- | 1 | 1 | | 2 | 25 | | 3 | 33 | | 4 | 150 |
回答by Niels
Try this:
尝试这个：
SET @rownr=0;
SELECT @rownr:=@rownr+1 AS rowNumber, rowID
FROM myTable
WHERE CategoryID = 1
In modern versions of MySQL you can use MySQL Window: http://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/
在现代版本的 MySQL 中，您可以使用 MySQL 窗口：http: //www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/
Example:
例子：
SELECT ROW_NUMBER() OVER ( ORDER BY productName ) row_num,
productName, msrp
FROM products ORDER BY productName;
回答by Arrabi
But what if you have a group byin the select statement? the counting will be off.
但是如果你group by在 select 语句中有 a呢？计数将停止。
For such cases, the only solution I found is nesting select:
对于这种情况，我找到的唯一解决方案是嵌套选择：
SELECT (@cnt := @cnt + 1) AS rowNumber, t.*
from
(select
t.rowID
FROM myTable
WHERE CategoryID = 1
ORDER BY rowID) t
CROSS JOIN (SELECT @cnt := 0) AS dummy
