如何在以下过程中使用ROW_NUMBER？

I have the following stored procedure which returns A, B, and the count in descending order. I am trying to use ROW_NUMBER, so I can page the records, but I want the first row number 1 to be the record with the highest count, so basically, if I return a table with 3 records and the count is 30, 20, 10, then row number 1 should correspond with count 30, row number 2 should correspond with count 20, and row number 3 should correspond with count 10. dbo.f_GetCount is a function that returns a count.

我有以下存储过程,它按降序返回A,B和计数。我正在尝试使用ROW_NUMBER,所以我可以对记录进行分页,但是我希望第一行编号1是具有最高计数的记录,所以基本上,如果我返回一个包含3条记录且计数为30,20的表,如图10所示,则行号1应对应于计数30,行号2应对应于计数20,行号3应对应于计数10.dbo.f_GetCount是返回计数的函数。

create procedure dbo.Test
as
@A nvarchar(300) = NULL,
@B nvarchar(10) = NULL
as

select @A = nullif(@A,'')
      ,@B = nullif(@B,'');

select h.A
      ,hrl.B
      ,dbo.f_GetCount(hrl.A,h.B) as cnt
from dbo.hrl
    inner join dbo.h
        on h.C = hrl.C
where(@A is null
      or h.A like '%'+@A+'%'
     )
     and (@B is null
          or hrl.B = @B
         )
group by hrl.B
        ,h.A
order by cnt desc;

3 个解决方案

#1

WITH q AS
        (
        SELECT h.A, hrl.B,
              dbo.f_GetCount(hrl.A,h.B) as cnt
        FROM dbo.hrl
        INNER JOIN dbo.h on h.C = hrl.C
        WHERE (@A IS NULL OR h.A like '%' + @A + '%') 
          AND (@B IS NULL OR hrl.B = @B)
        GROUP BY hrl.B, h.A
        )
SELECT  q.*, ROW_NUMBER() OVER (ORDER BY cnt DESC) AS rn
FROM    q
ORDER BY rn DESC

To retrieve first 10 rows, use:

要检索前10行,请使用:

WITH q AS
        (
        SELECT h.A, hrl.B,
              dbo.f_GetCount(hrl.A,h.B) as cnt
        FROM dbo.hrl
        INNER JOIN dbo.h on h.C = hrl.C
        WHERE (@A IS NULL OR h.A like '%' + @A + '%') 
          AND (@B IS NULL OR hrl.B = @B)
        GROUP BY hrl.B, h.A
        )
SELECT  TOP 10 q.*, 
        ROW_NUMBER() OVER (ORDER BY cnt DESC, A, B) AS rn
FROM    q
ORDER BY cnt DESC, A, B

To retrieve rows between 11 and 20, use:

要检索11到20之间的行,请使用:

SELECT  *
FROM    (
        WITH q AS
                (
                SELECT h.A, hrl.B,
                      dbo.f_GetCount(hrl.A,h.B) as cnt
                FROM dbo.hrl
                INNER JOIN dbo.h on h.C = hrl.C
                WHERE (@A IS NULL OR h.A like '%' + @A + '%') 
                  AND (@B IS NULL OR hrl.B = @B)
                GROUP BY hrl.B, h.A
                )
        SELECT  q.*, 
                ROW_NUMBER() OVER (ORDER BY cnt DESC, A, B) AS rn
        FROM    q
        ) qq
WHERE rn BETWEEN 11 AND 20
ORDER BY cnt DESC, A, B

#2

I would use a sub-query to get the values of the function into the result, and then the ROW_NUMBER ranking function, like so:

我会使用子查询将函数的值输入结果,然后使用ROW_NUMBER排名函数,如下所示:

select
    ROW_NUMBER() over (order by t.cnt desc) as RowId, t.*
from
    (
        SELECT
            h.A, hrl.B, dbo.f_GetCount(hrl.A,h.B) as cnt
        FROM
            dbo.hrl
                INNER JOIN dbo.h on h.C = hrl.C
        WHERE 
            (@A IS NULL OR h.A like '%' + @A + '%') AND 
            (@B IS NULL OR hrl.B = @B)
        GROUP BY
            hrl.B, h.A
    ) as t
order by
    1

If you wanted only a certain section of results (say, for paging), then you would need another subquery, and then filter on the row number:

如果您只想要某一部分结果(例如,用于分页),那么您需要另一个子查询,然后对行号进行过滤:

select
    t.*
from
    (
        select
            ROW_NUMBER() over (order by t.cnt desc) as RowId, t.*
        from
            (
                SELECT
                    h.A, hrl.B, dbo.f_GetCount(hrl.A,h.B) as cnt
                FROM
                    dbo.hrl
                        INNER JOIN dbo.h on h.C = hrl.C
                WHERE 
                    (@A IS NULL OR h.A like '%' + @A + '%') AND 
                    (@B IS NULL OR hrl.B = @B)
                GROUP BY
                    hrl.B, h.A
            ) as t
    ) as t
where
    t.RowId between 1 and 10
order by
    t.RowId

Note that in this query, you could put ROW_NUMBER anywhere in the select list, since you are no longer reliant on using the "order by 1" syntax for the order by statement.

请注意,在此查询中,您可以将ROW_NUMBER放在选择列表中的任何位置,因为您不再依赖于order by语句使用“order by 1”语法。

There is a subtle issue here when calling this query multiple times. It is not guaranteed that the order in which the records are returned are going to be consistent if the number of items in each group is not unique. In order to address this, you have to change the ROW_NUMBER function to order on the fields that make up the group in the count.

多次调用此查询时,此处存在一个微妙的问题。如果每个组中的项目数不唯一,则无法保证返回记录的顺序将保持一致。为了解决这个问题,您必须更改ROW_NUMBER函数以对构成计数组的字段进行排序。

In this case, it would be A and B, resulting in:

在这种情况下,它将是A和B,导致:

select
    t.*
from
    (
        select
            ROW_NUMBER() over (order by t.cnt desc, t.A, t.B) as RowId, t.*
        from
            (
                SELECT
                    h.A, hrl.B, dbo.f_GetCount(hrl.A,h.B) as cnt
                FROM
                    dbo.hrl
                        INNER JOIN dbo.h on h.C = hrl.C
                WHERE 
                    (@A IS NULL OR h.A like '%' + @A + '%') AND 
                    (@B IS NULL OR hrl.B = @B)
                GROUP BY
                    hrl.B, h.A
            ) as t
    ) as t
where
    t.RowId between 1 and 10
order by
    t.RowId

This ends up ordering the results consistently between calls when the count of the items between groups is not unique (assuming the same set of data).

当组之间的项目计数不唯一时(假设相同的数据集),这最终会在调用之间一致地排序结果。

#3

SELECT h.A, hrl.B,
       dbo.f_GetCount(hrl.A,h.B) as cnt,
ROW_NUMBER() over (order by cnt desc) as row_num
FROM dbo.hrl
INNER JOIN dbo.h on h.C = hrl.C
WHERE (@A IS NULL OR h.A like '%' + @A + '%') 
  AND (@B IS NULL OR hrl.B = @B)
GROUP BY hrl.B, h.A
ORDER BY cnt desc

This should do the trick. I don't have SSMS in front of me to test, but you MAY have to substitute the usage of 'cnt' in the ROW_NUMBER's order by clause with a second call to the function, but this should give you the general idea.

这应该可以解决问题。我没有在我面前测试SSMS,但你可能必须用ROW_NUMBER的order by子句替换'cnt'的用法并再次调用该函数,但这应该给你一般的想法。