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

本文介绍了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 |