MySQL:在select语句中自动增加临时列 [英] MySQL: Auto increment temporary column in select statement

问题描述

如何使用MySQL在我的select语句中创建并自动增加一个临时列?

How do I create and auto increment a temporary column in my select statement with MySQL?

这是我到目前为止所拥有的:

Here is what I have so far:

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 +
+  ...      |   ... +
+++++++++++++++++++++

推荐答案

这将为您提供 连续 行号，并带有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 |

这篇关于MySQL:在select语句中自动增加临时列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！