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

I have the following Db and query. The query takes two parameters: the sort column and the direction. However, I have to add custom sorting to the query (based on the Fuji should come first and Gala second, etc.). This part also work but it create duplicated code in my query. Because of that, I am pretty sure people will not let me check this in. So my question is: is there a way to not duplicate the CASE statement?

我有以下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

Thanks!

3 个解决方案

#1

You could multiply the sorting key by either +1 or -1 depending on whether ASC or DESC is requested:

您可以将排序键乘以+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

#2

Since you're on SQL 2008, you could use a CTE:

由于您使用的是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

It's not as slick as the *-1 solution for this particular problem, but it can be adapted for other situations where you want to avoid code duplication.

它不像这个特定问题的* -1解决方案那样光滑,但它可以适用于您希望避免代码重复的其他情况。

#3

Why not have another table for comparable value, join on the variety column, and sort on the comparison value.

为什么不使用另一个表来获得可比较的值,在品种列上加入,并对比较值进行排序。

E.g.

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

Wouldn't that do the trick also while being a bit more database-y?

在成为更多数据库的时候,这也不会成功吗?

I'm not very practiced, so the syntax is probably quite broken. I am quite dissatisfied with the concept of 'CASE' statements in SQL though.

我不是很练习,所以语法可能很破碎。我对SQL中'CASE'语句的概念非常不满意。

#1