您可以通过使用几个排名功能来实现。我们ROW_NUMBER()在中间和另一列中使用“平局决胜法”：
declare @maxRows int
set @maxRows = 5
; With InitialRanks as (
select DENSE_RANK() OVER (ORDER BY type) as rnk,* from sys.objects
), OrderedRanks as (
select (ROW_NUMBER() OVER (PARTITION BY rnk ORDER by object_id)-1)
/ @maxRows as rn,*
from InitialRanks
)
select DENSE_RANK() OVER (ORDER BY rnk,rn),* from OrderedRanks
在这里，每个（最终）等级值最多只能显示5列。排名基于，type但我们object_id用作辅助列来确定行被授予特定排名的顺序。
原来，我使以上内容过于复杂- 不需要第一个CTE和第一个CTE，DENSE_RANK因为它可以有效地充当函数中type列的代理ROW_NUMBER()-因此，为什么不直接使用该type列并简化操作：
declare @maxRows int
set @maxRows = 5
; With OrderedRanks as (
select (ROW_NUMBER() OVER (PARTITION BY type ORDER by object_id)-1)
/ @maxRows as rn,*
from sys.objects
)
select DENSE_RANK() OVER (ORDER BY type,rn),* from OrderedRanks
602392714
清零编程群