本文介绍了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 |
这篇关于MySQL:选择语句中的自动增量临时列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！