MySQL:选择语句中的自动增量临时列 [英] MySQL: Auto increment temporary column in select statement

问题描述

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

这是我目前所拥有的:

SET @cnt = 0;选择(@cnt =@cnt + 1) AS rowNumber,行号从我的表类别 ID = 1

返回:

+++++++++++++++++++++++ 行号 |行号 +++++++++++++++++++++++++ (NULL) |1 ++ (NULL) |25 ++ (NULL) |33 ++ (NULL) |150++ (NULL) |219 ++++++++++++++++++++++++

但我需要:

+++++++++++++++++++++++ 行号 |行号 +++++++++++++++++++++++++ 1 |1 ++ 2 |25 ++ 3 |33 ++ 4 |150++ ... |... ++++++++++++++++++++++++

解决方案

这会给你一个连续行号和3.

SELECT(@cnt := @cnt + 1) AS rowNumber,行号从 myTable AS tCROSS JOIN (SELECT @cnt := 0) AS 虚拟哪里 t.CategoryID = 1按 t.rowID 排序；

结果

<前>|行号 |行列 |---------------------|1 |1 ||2 |25 ||3 |33 ||4 |150 |

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

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

解决方案

This will give you a consecutive row number with 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 |

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