MySQL：在select语句中自动增加临时列

• Z时代
• 2024-01-10
• 分类：问答

如何在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 +
+  ...      |   ... +
+++++++++++++++++++++

回答：

这将为您提供一个 行号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语句中自动增加临时列 的全部内容， 来源链接： utcz.com/qa/435226.html