Пользовательский порядок сортировки - как не дублировать оператор Case

Question

У меня есть следующий Db и запрос. Запрос принимает два параметра: столбец сортировки и направление. Тем не менее, я должен добавить пользовательскую сортировку к запросу (на основе Fuji должно идти первым, а Gala второе и т. Д.). Эта часть также работает, но она создает дублированный код в моем запросе. Из-за этого я почти уверен, что люди не позволят мне проверить это. Поэтому мой вопрос: есть ли способ не дублировать утверждение CASE?

CREATE TABLE Fruits (
    [type] nvarchar(250),
    [variety] nvarchar(250),
    [price] money
)
GO

INSERT INTO Fruits VALUES ('Apple', 'Gala', 2.79)
INSERT INTO Fruits VALUES ('Apple', 'Fuji', 0.24)
INSERT INTO Fruits VALUES ('Apple', 'Limbertwig', 2.87)
INSERT INTO Fruits VALUES ('Orange', 'Valencia', 3.59)
INSERT INTO Fruits VALUES ('Pear', 'Bradford', 6.05)

DECLARE @sortColumnName nvarchar(MAX) = 'Variety'
DECLARE @sortDirection nvarchar(MAX) = 'ASC'

SELECT ROW_NUMBER() OVER (ORDER BY                   
    CASE WHEN @sortColumnName = 'Variety' AND @sortDirection = 'ASC' 
        THEN 
            CASE f.Variety
                WHEN 'Fuji' THEN 1
                WHEN 'Gala' THEN 2
                ELSE 3
            END     
        END ASC,
    CASE WHEN @sortColumnName = 'Variety' AND @sortDirection = 'DESC' 
        THEN 
            CASE f.Variety
                WHEN 'Fuji' THEN 1
                WHEN 'Gala' THEN 2
                ELSE 3
            END     
        END DESC), *
FROM   Fruits f

Answer 1

Вы можете умножить ключ сортировки на +1 или -1 в зависимости от того, запрашивается ли ASC или DESC:

SELECT ROW_NUMBER() OVER (ORDER BY                   
    CASE WHEN @sortColumnName = 'Variety'
         THEN 
            (CASE f.Variety
                WHEN 'Fuji' THEN 1
                WHEN 'Gala' THEN 2
                ELSE 3
            END)     
    END
    * (CASE WHEN @sortDirection = 'ASC' THEN 1 ELSE -1 END)), *
FROM   Fruits f

Answer 2

Поскольку вы работаете с SQL 2008, вы можете использовать CTE:

;WITH CTE AS
(
    SELECT
        CASE WHEN @sortColumnName = 'Variety' THEN 
            CASE f.Variety
                WHEN 'Fuji' THEN 1
                WHEN 'Gala' THEN 2
                ELSE 3
            END     
        END AS sort_column,
        *
    FROM
        Fruits F
)
SELECT
    ROW_NUMBER() OVER (
        ORDER BY
            CASE WHEN @sortDirection = 'DESC' THEN sort_column ELSE 0 END DESC,
            CASE WHEN @sortDirection = 'ASC' THEN sort_column ELSE 0 END ASC),
    type,
    variety,
    price
FROM
    CTE

Он не так удобен, как решение *-1 для этой конкретной проблемы, но его можно адаптировать для других ситуаций, когда вы хотите избежать дублирования кода.

Answer 3

Почему бы не иметь другую таблицу для сопоставимого значения, присоединиться к столбцу сорта и отсортировать по значению сравнения.

Например

INSERT INTO FruitSort VALUES ('Gala', 2)
INSERT INTO FruitSort VALUES ('Fuji', 1)

SELECT ROW_NUMBER() OVER (ORDER BY FruitSort.sortvalue)
FROM   Fruits f
JOIN   FruitSort
   ON FruitSort.variety == Fruits.variety

Разве это не сработало бы, будучи немного более базой данных?

Я не очень практичен, поэтому синтаксис, вероятно, довольно нарушен. Хотя я совершенно не удовлетворен концепцией операторов CASE в SQL.