T-SQL“ dense_rank”每个等级的最大行数 [英] T-SQL "dense_rank" with a maximum number of rows with each rank

问题描述

如果我这样做了

dense_rank() over (order by colname),

列名中具有相同值的所有行的排名相同。

I get the same rank for all rows with the same value in the column colname.

但是，我想将具有相同等级的行数限制为@maxrows，以便当@maxrows行的colname值相同时，即使colname的值仍然是一样。

However, I want to limit the number of rows with the same rank to @maxrows so that when @maxrows rows have the same value in colname, a new rank is assigned to the next row even if the value of colname is still the same.

我该怎么实现？

推荐答案

您可以通过使用几个排名功能来实现。我们在中间使用 ROW_NUMBER（）并在另一列中使用抢七游戏：

You can achieve this via using several ranking functions. We use ROW_NUMBER() in the middle and another column to perform tie-breaking:

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列。排名基于类型，但是我们使用 object_id 作为第二列来确定允许行的顺序

Here I get only up to 5 columns with each (final) rank value. The ranking is based on type but we use object_id as a secondary column to work out the order in which rows are allowed a particular rank.

结果是，我使以上内容过于复杂-不需要第一个CTE和第一个 DENSE_RANK ，因为它实际上是 ROW_NUMBER（）中 type 列的代理函数-为什么不直接使用 type 列并简化操作：

Turns out I'd over-complicated the above - there's no need for the first CTE and the first DENSE_RANK since that's effectively acting as a proxy for the type column in the ROW_NUMBER() function - so why not just use the type column directly and simplify things:

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

这篇关于T-SQL“ dense_rank”每个等级的最大行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！