MySQL:选择语句中的自动增量临时列
如何在 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 |
相关文章