如何获取某条记录在查询结果中的顺序号

[aa323aa323]

如果表中有100条记录
满足某条件的50条
怎么能获取这50条中的具体某一条在这50中顺序排多少号？

[二月十六]

加一个

ROW_NUMBER()OVER(ORDER BY 列名)

例如：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([name] NVARCHAR(100))
Insert #T SELECT '测试'+RTRIM(number) FROM master.dbo.spt_values
                    WHERE   type = 'P' AND number<100
Go
--测试数据结束
Select *,ROW_NUMBER()OVER(ORDER BY name) AS num from #T

[aa323aa323]

可能没表达清楚
比如
想获取a10在表中B组的顺序，
结果应该是3，就是排在第三个。这个语句怎么写？

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[品名] nvarchar(23),[组别] nvarchar(22))
Insert #T
select 33,N'a1',N'A组' union all
select 40,N'a8',N'B组' union all
select 41,N'a9',N'B组' union all
select 42,N'a10',N'B组' union all
select 43,N'a11',N'B组'
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( ORDER BY ( SELECT   1
                                     ) ) AS 顺序
FROM    #T
WHERE [组别] = 'B组'
)
SELECT * FROM cte WHERE  品名='a10'

[二月十六]

或者这样

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[品名] nvarchar(23),[组别] nvarchar(22))
Insert #T
select 33,N'a1',N'A组' union all
select 40,N'a8',N'B组' union all
select 41,N'a9',N'B组' union all
select 42,N'a10',N'B组' union all
select 43,N'a11',N'B组'
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY [组别] ORDER BY ( SELECT 1) ) AS 顺序
FROM    #T
)
SELECT * FROM cte WHERE [组别] = 'B组' AND 品名='a10'

[二月十六]

如果是按照id降序排序计算的话这样

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[品名] nvarchar(23),[组别] nvarchar(22))
Insert #T
select 33,N'a1',N'A组' union all
select 40,N'a8',N'B组' union all
select 41,N'a9',N'B组' union all
select 42,N'a10',N'B组' union all
select 43,N'a11',N'B组'
Go
--测试数据结束
;WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY [组别] ORDER BY id ) AS 顺序
FROM    #T
)
SELECT * FROM cte WHERE [组别] = 'B组' AND 品名='a10'

[aa323aa323]

牛！

[二月十六]

引用 6 楼 aa323aa323 的回复:

牛！

如果帮到了你记得结贴