自定义排序顺序-如何不重复Case语句

Question

我有以下Db和查询。该查询接受两个参数:排序列和方向。但是，我必须向查询添加自定义排序(基于富士应该是第一个，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

根据请求的是ASC还是DESC，可以将排序关键字乘以+1或-1：

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

由于您使用的是SQL2008，因此可以使用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

这难道不是在更多地使用数据库的同时也能做到这一点吗？

我不是很熟练，所以语法可能很糟糕。不过，我对SQL中“CASE”语句的概念很不满意。