带有 Desc/Asc 排序的 Order By 子句的 Case 语句

问题描述

SELECT *
FROM
    TableName
WHERE
ORDER BY 
    CASE @OrderByColumn
    WHEN 1 THEN Forename
    WHEN 2 THEN Surname
    END;

我有一个类似上面的语句，它让我可以动态地选择如何对查询结果进行排序.但是，如何指定我想要 Forename 订购的 DESC 和 Surname ASC?

I have a statement like above which lets me dynamically choose how to order the results of a query. However, how do I specify that I want the Forename ordered DESC and the Surname ASC?

推荐答案

你需要把你的ORDER BY分成两部分:

You need to split your ORDER BY in two parts:

SELECT *
FROM
    TableName
WHERE
ORDER BY 
    (CASE @OrderByColumn
    WHEN 1 THEN Forename
    END) DESC -- Forename --> descending
,   (CASE @OrderByColumn
    WHEN 2 THEN Surname
    END) ASC -- Surname --> ascending