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

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行,请使用:

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,请使用:

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