向此 T-SQL 查询添加行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9150378/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 14:15:29 来源:igfitidea点击:
Add row number to this T-SQL query
提问by Mario M
How can I add ROW numbers to this query result?
如何将 ROW 编号添加到此查询结果中？
SELECT DISTINCT
VehicleSpecs.SubmittedById,
COUNT(VehicleSpecs.SubmittedById) AS NumCars,
aspnet_Users.UserName
FROM
VehicleSpecs
INNER JOIN aspnet_Users ON VehicleSpecs.SubmittedById = aspnet_Users.UserId
WHERE
(LEN(VehicleSpecs.SubmittedById) > 0)
GROUP BY
VehicleSpecs.SubmittedById,
aspnet_Users.UserName
ORDER BY
NumCars DESC
采纳答案by Tomek
Add: ROW_NUMBER() OVER (ORDER BY NumCars)
添加： ROW_NUMBER() OVER (ORDER BY NumCars)
EDIT:
编辑：
WITH t1 AS
( SELECT DISTINCT
VehicleSpecs.SubmittedById ,
COUNT(VehicleSpecs.SubmittedById) AS NumCars ,
aspnet_Users.UserName
FROM VehicleSpecs
INNER JOIN aspnet_Users ON VehicleSpecs.SubmittedById = aspnet_Users.UserId
WHERE ( LEN(VehicleSpecs.SubmittedById) > 0 )
GROUP BY VehicleSpecs.SubmittedById ,
aspnet_Users.UserName
)
SELECT ROW_NUMBER() OVER ( ORDER BY NumCars ), *
FROM t1
ORDER BY NumCars
回答by Mikael Eriksson
Wrap you entire query in a sub query and add row_numberin the outer query.
将整个查询包装在一个子查询中并添加row_number到外部查询中。
select *, row_number() over(order by (select 0)) as rn
from
(
select distinct -- your columns
from YourTable
) as T
order by NumCars desc
