Как использовать ROW_NUMBER в следующей процедуре?

У меня есть следующая хранимая процедура, которая возвращает 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 ответа

Решение
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
Источник

Я бы использовал подзапрос, чтобы получить значения функции в результате, а затем функцию ранжирования 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

Если вам нужен только определенный раздел результатов (скажем, для подкачки страниц), вам потребуется другой подзапрос, а затем выполнить фильтрацию по номеру строки:

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

Обратите внимание, что в этом запросе вы можете поместить ROW_NUMBER в любом месте списка выбора, так как вы больше не зависите от использования синтаксиса "order by 1" для оператора order by.

При вызове этого запроса несколько раз возникает небольшая проблема. Не гарантируется, что порядок, в котором возвращаются записи, будет согласованным, если количество элементов в каждой группе не уникально. Чтобы решить эту проблему, вы должны изменить функцию ROW_NUMBER на порядок полей, которые составляют группу в счетчике.

В этом случае это будут 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

Это приводит к последовательному упорядочению результатов между вызовами, когда количество элементов между группами не является уникальным (при условии, что один и тот же набор данных).

Источник 
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

Это должно сделать свое дело. У меня нет SSMS передо мной для тестирования, но вы МОЖЕТЕ заменить использование 'cnt' в предложении ROW_NUMBER на предложение вторым вызовом функции, но это должно дать вам общее представление.

Источник
Другие вопросы по тегам