SQL 如何枚举SQL中返回的行？

How to enumerate returned rows in SQL?

提问by SilentGhost

I was wondering if it would be possible to enumerate returned rows. Not according to any column content but just yielding a sequential integer index. E.g.

我想知道是否可以枚举返回的行。不根据任何列内容，而只是产生一个连续的整数索引。例如

select ?, count(*) as usercount from users group by age

would return something along the lines:

会返回一些东西：

1    12
2    78
3     4
4    42

it is for https://data.stackexchange.com/

它适用于https://data.stackexchange.com/

采纳答案by KM.

try:

尝试：

SELECT
    ROW_NUMBER() OVER(ORDER BY age) AS RowNumber
        ,count(*) as usercount 
    from users 
    group by age

回答by FrustratedWithFormsDesigner

If it's Oracle, use rownum.

如果是 Oracle，请使用rownum.

SELECT SOMETABLE.*, ROWNUM RN
FROM SOMETABLE
WHERE SOMETABLE.SOMECOLUMN = :SOMEVALUE
ORDER BY SOMETABLE.SOMEOTHERCOLUMN;

The final answer will entirely depend on what database you're using.

最终答案将完全取决于您使用的数据库。

回答by BenV

For MySql:

对于 MySQL：

SELECT  @row := @row + 1 as row FROM anytable a, (SELECT @row := 0) r

回答by Alaroff

In contrast to majority of other answers, and in accordance of the actual OP question, to

与大多数其他答案相反，根据实际的 OP 问题，

enumerate returned rows (...) NOTaccording to any column content

枚举返回的行 (...)不根据任何列内容

but rather in the order of returned query, one could use a dummy ordinal variable to ORDER BYin an ROW_NUMBERfunction, e.g.

而是按照返回查询的顺序，可以ORDER BY在ROW_NUMBER函数中使用虚拟序数变量，例如

ROW_NUMBER() OVER(ORDER BY (SELECT 0) AS row_num

where one could actually use anything as an argument to the SELECT-statement, like SELECT 100, SELECT ‘A', SELECT NULL, etc..

其中一个实际上可以使用任何东西作为参数的SELECT语句来，像SELECT 100，SELECT ‘A'，SELECT NULL，等。

This way, the row numbers will be enumerated in the same order the data was added to the table.

这样，行号将按照将数据添加到表中的相同顺序进行枚举。

回答by Andomar

How you'd do that depends on your database server. In SQL Server, you could use row_number():

你怎么做取决于你的数据库服务器。在 SQL Server 中，您可以使用row_number()：

select  row_number() over (order by age)
,       age
,       count(*) as usercount 
from    users 
group by 
        age
order by
        age

But it's often easier and faster to use client side row numbers.

但是使用客户端行号通常更容易、更快。

回答by Pranay Rana

use rownumberfunction available in sql server

使用sql server中可用的rownumber函数

SELECT 
    ROW_NUMBER() OVER (ORDER BY columnNAME) AS 'RowNumber',count(*) as usercount
    FROM users

回答by srbcheema1

for Mysql

为了 Mysql

set @row:=0;

select @row:=@row+1 as row, a.* from table_name as a;