MySQL:在select语句中自动增加临时列 [英] MySQL: Auto increment temporary column in select statement
本文介绍了MySQL:在select语句中自动增加临时列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何使用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屋!
查看全文