如何在以下过程中使用 ROW_NUMBER? [英] How to use ROW_NUMBER in the following procedure?

问题描述

我有以下存储过程，它按降序返回 A、B 和计数.我正在尝试使用 ROW_NUMBER，所以我可以分页记录，但我希望第一行号 1 是计数最高的记录，所以基本上，如果我返回一个有3条记录的表，计数为30、20、10，那么行号1应该对应计数30，行号2对应计数20，行号3对应计数<代码>10.dbo.f_GetCount 是一个返回计数的函数.

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.

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;

推荐答案

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

要检索前 10 行，请使用:

To retrieve first 10 rows, use:

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

要检索 11 和 20 之间的行，请使用:

To retrieve rows between 11 and 20, use:

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

这篇关于如何在以下过程中使用 ROW_NUMBER?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！