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

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 |