sql - 带 DES/Asc 排序的 Order By 子句的 Case 语句

Question

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

我有一个像上面这样的语句，它可以让我动态选择如何对查询结果进行排序。但是，如何指定我希望名字按 DESC 排序，姓氏 ASC 排序？

Answer 1

您需要将 ORDER BY 分成两部分:

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