Динамический порядок T-SQL в общей таблице для разбивки на страницы

ProgramBox

Динамический порядок T-SQL в общей таблице для разбивки на страницы

Post author:admin
Запись опубликована:30 апреля, 2023
Post category:Вопросы по программированию

#tsql #pagination #dynamic-sql #common-table-expression

#tsql #разбивка на страницы #dynamic-sql #common-table-expression

Вопрос:

Я нашел отличный sql-код для динамической сортировки и порядка, но мне интересно, может ли кто-нибудь помочь мне переделать его так, чтобы CTE в приведенной ниже процедуре использовал динамическую сортировку / порядок. Этот код выполняется, но вывод не тот, который мне нужен, поскольку ПОРЯДОК ПО p.ProductID выполняется первым в CTE, затем оператор ORDER BY CASE применяется только к записям с 6 по 10

 DECLARE @Skip int
DECLARE @Take int
DECLARE @OrderBy VARCHAR(50)

SET @Skip = 5;
SET @Take = 5;
SET @OrderBy = 'PriceAsc';


WITH ProductCT AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY p.ProductId) AS RowNum
    , p.ProductId
    FROM dbo.Product AS p
)
SELECT   p.ProductId
        ,p.Title
        ,p.Price
FROM dbo.Product AS p
INNER JOIN ProductCT AS pct ON pct.ProductId = p.ProductId
WHERE pct.RowNum BETWEEN @Skip   1 AND (@Skip   @Take)
ORDER BY
    CASE     
         WHEN @OrderBy = 'TitleAsc' THEN (RANK() OVER (ORDER BY p.Title))
         WHEN @OrderBy = 'TitleDesc' THEN (RANK() OVER (ORDER BY p.Title DESC))
         WHEN @OrderBy = 'PriceAsc' THEN (RANK() OVER (ORDER BY p.Price))
         WHEN @OrderBy = 'PriceDesc' THEN (RANK() OVER (ORDER BY p.Price DESC))
         ELSE (RANK() OVER (ORDER BY p.Price))
    END

Заранее спасибо за любые предложения 🙂

1. Почему вы используете RANK() в ORDER BY ?

2. Потому что это единственный способ, который я смог найти, позволяющий добавить ASC / DESC к инструкции CASE. Я нашел код в Google, он работает — если есть способ получше, пожалуйста, дайте мне знать 🙂

Ответ №1:

Изначально я пометил ответ от @Johan как правильный, потому что он сработал, но я был немного неуверен во ВНУТРЕННЕМ СОЕДИНЕНИИ и общей сложности этого запроса.

Я поговорил со своим коллегой о проблеме, и он предложил это очень аккуратное решение (спасибо, Том !!), поэтому я подумал, что поделюсь им:

 DECLARE @Skip int
DECLARE @Take int
DECLARE @OrderBy VARCHAR(50)

SET @Skip = 5;
SET @Take = 5;
SET @OrderBy = 'PriceAsc';


WITH ProductCT AS
(
    SELECT
    CASE
       WHEN @OrderBy = 'TitleAsc' THEN ROW_NUMBER() OVER (ORDER BY p.Title)
       WHEN @OrderBy = 'TitleDesc' THEN ROW_NUMBER() OVER (ORDER BY p.Title DESC)
       WHEN @OrderBy = 'PriceAsc' THEN ROW_NUMBER() OVER (ORDER BY p.Price)
       WHEN @OrderBy = 'PriceDesc' THEN ROW_NUMBER() OVER (ORDER BY p.Price DESC)
    END AS RowNum
    , p.ProductId
    FROM dbo.Product AS p
)
SELECT   p.ProductId
        ,p.Title
        ,p.Price
FROM dbo.Product AS p
INNER JOIN ProductCT AS pct ON pct.ProductId = p.ProductId
WHERE pct.RowNum BETWEEN @Skip   1 AND (@Skip   @Take)
ORDER BY RowNum

1. Неплохо. Намного лучше, чем у меня.

2. Ты все-таки вывел нас на правильный путь, Йохан, так что спасибо за помощь 🙂

3. Нет проблем. Кстати, теперь я заметил, что ROW_NUMBER() все вы используете p.Title . Возможно, опечатка … 🙂

4. Спасибо, я исправил их — пойман монстром копирования и вставки 🙂

5. Единственное решение, которое я нашел, которое работало, не используя динамический sql

Ответ №2:

Вот решение, которое должно сработать.

 WITH ProductCT AS
(
    SELECT  ROW_NUMBER() OVER(ORDER BY p.Title) AS RowNum1
            ,ROW_NUMBER() OVER(ORDER BY p.Title DESC) AS RowNum2
            ,ROW_NUMBER() OVER(ORDER BY p.Price) AS RowNum3
            ,ROW_NUMBER() OVER(ORDER BY p.Price DESC) AS RowNum4
            ,p.ProductId
    FROM dbo.Product AS p
)
SELECT   p.ProductId
        ,p.Title
        ,p.Price
FROM dbo.Product AS p
INNER JOIN ProductCT AS pct 
    ON  pct.ProductId = p.ProductId
    AND ((@OrderBy = 'TitleAsc' AND pct.RowNum1 BETWEEN @Skip   1 AND (@Skip   @Take))
    OR  (@OrderBy = 'TitleDesc' AND pct.RowNum2 BETWEEN @Skip   1 AND (@Skip   @Take))
    OR  (@OrderBy = 'PriceAsc' AND pct.RowNum3 BETWEEN @Skip   1 AND (@Skip   @Take))
    OR  (@OrderBy = 'PriceDesc' AND pct.RowNum4 BETWEEN @Skip   1 AND (@Skip   @Take))
    )
ORDER BY
    CASE @OrderBy
         WHEN 'TitleAsc' THEN RowNum1
         WHEN 'TitleDesc' THEN RowNum2
         WHEN 'PriceAsc' THEN RowNum3
         WHEN 'PriceDesc' THEN RowNum4
         ELSE RowNum3
    END

Я думаю, что JOIN можно было бы написать лучше, но у меня сейчас нет больше времени.

Ответ №3:

Решения Роба почему-то не работают для меня, вот моя модифицированная версия, которая работала нормально.

        SELECT
          ROW_NUMBER() OVER (ORDER BY 
    CASE
       WHEN @sortOrder = 'TitleAsc' THEN p.Title
       WHEN @sortOrder = 'TitleDesc' THEN  p.Title
       WHEN @sortOrder = 'PatientId' THEN  p.Title
       WHEN @sortOrder = 'PriceAsc' THEN  p.Title
       WHEN @sortOrder = 'PriceDesc' THEN  p.Title
    END ASC) AS [RowNum],
       .................
      ..................