将行号添加到此T-SQL查询
将行号添加到此T-SQL查询
提问于 2012-02-05 23:19:17
如何在此查询结果中添加行号？
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回答 2
Stack Overflow用户
回答已采纳
发布于 2012-02-05 23:24:33
添加：ROW_NUMBER() OVER (ORDER BY NumCars)
编辑：
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 NumCarsStack Overflow用户
发布于 2012-02-05 23:42:03
将整个查询包装在子查询中，并在外部查询中添加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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9150378
复制相关文章
相似问题