如何在SQL中枚举返回的行?

尝试:

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

如果是甲骨文,用 rownum .

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

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

对于MySql:

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

rownumber sql server中可用的函数

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

如何做到这一点取决于您的数据库服务器。在SQL Server中,可以使用 row_number()

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

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

对于 Mysql

set @row:=0;

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

对比其他大多数答案,并根据实际操作问题

不是

ORDER BY 在一个 ROW_NUMBER 功能,例如。

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

我们可以用任何东西作为 SELECT -声明,比如 SELECT 100 SELECT ‘A’ SELECT NULL

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