将编号列表列添加到返回的MySQL查询中 [英] Add a numbered list column to a returned MySQL query

问题描述

我正在尝试获取随我的sql查询返回的序列号列.我需要将它放在SELECT语句中，因为我想将此查询嵌套在另一个查询中，然后将之前生成的列用于进一步的计算.

I am trying to get a sequential number column returned with my sql query. I need this to be inside the SELECT statement because I want to nest this query in another, and then use the column generated before for some further calculations.

我到处都看过，找不到任何例子.

I have looked everywhere and I can't find any examples of this.

具有以下效果的事物:

SELECT *, ROW_NUMBER() as row_number from my_awesome_table;

这应该生成如下内容:

row_number some_column some_other_column
---------- ----------- -----------------
    1        bla bla      bla bla bla 
    2        bla bla      bla bla bla 
    3        bla bla      bla bla bla 
    4        bla bla      bla bla bla 

请注意，some_column和some_other_column是my_awesome_table中实际存在的列，而row_number仅针对此查询即时生成.

Note that some_column and some_other_column are real existing columns in my_awesome_table, whereas row_number is generated on-the-fly for this query only.

谢谢！ 猪

推荐答案

您需要使用 MySQL用户变量.让我们采用变量t1.在0对其进行初始化.并在每个选择值中将其递增.

You need to use MySQL user variables. Let us take a variable t1. Initialize it at 0. And increment it in every select value.

SET @t1=0;
SELECT @t1 := @t1+1 as row_number, my_awesome_table.* from my_awesome_table

这可以解决问题.

这篇关于将编号列表列添加到返回的MySQL查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！