MySQL的：在select语句自动递增临时列(MySQL: Auto increment temp

如何我在与MySQL的SELECT语句创建并自动递增一个临时列？

这是我到目前为止有：

SET @cnt = 0;
SELECT
    (@cnt =@cnt + 1) AS rowNumber,
    rowID
FROM myTable
WHERE CategoryID = 1

返回：

+++++++++++++++++++++
+ rowNumber | rowID +
+++++++++++++++++++++
+  (NULL)   |   1   +
+  (NULL)   |   25  +
+  (NULL)   |   33  +
+  (NULL)   |   150 +
+  (NULL)   |   219 +
+++++++++++++++++++++

但是我需要：

+++++++++++++++++++++
+ rowNumber | rowID +
+++++++++++++++++++++
+  1        |   1   +
+  2        |   25  +
+  3        |   33  +
+  4        |   150 +
+  ...      |   ... +
+++++++++++++++++++++

Answer 1:

这会给你一个连续的行号与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 ;

结果

| ROWNUMBER | ROWID |
---------------------
|         1 |     1 |
|         2 |    25 |
|         3 |    33 |
|         4 |   150 |

Answer 2:

试试这个：

SET @rownr=0;
SELECT @rownr:=@rownr+1 AS rowNumber, rowID
  FROM myTable
  WHERE CategoryID = 1

在MySQL的现代版本，你可以使用MySQL窗口： http://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/

例：

SELECT  ROW_NUMBER() OVER (  ORDER BY productName  ) row_num, 
        productName, msrp 
FROM   products ORDER BY   productName;

Answer 3:

但是，如果你有一个group by在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

文章来源: MySQL: Auto increment temporary column in select statement