使用Desc/Asc排序的Order By子句声明
SELECT *
FROM
TableName
WHERE
ORDER BY
CASE @OrderByColumn
WHEN 1 THEN Forename
WHEN 2 THEN Surname
END;
我有一个类似上面的声明,它让我动态选择如何排序查询结果。但是,如何指定我要订购的姓氏DESC
和姓氏ASC
?使用Desc/Asc排序的Order By子句声明
你需要分割你的两个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
投了赞,谢谢! – 2014-09-20 15:01:59
我可以在我的order by子句中使用多个列吗?例如'CASE @OrderByColumn WHEN 1 THEN Forename,Date END DESC' – 2014-09-25 22:07:18
@volumeone不是那样的,没有。 “CASE”表达式返回单个值,因此您需要在逗号后用第二列重复该情况。 – dasblinkenlight 2014-09-25 23:31:58
您需要在order by
两个子句:
ORDER BY (CASE WHEN @OrderByColumn = 1 and @Dir = 'ASC' THEN Forename
WHEN @OrderByColumn = 2 and @Dir = 'ASC' THEN Surname
END) ASC,
(CASE WHEN @OrderByColumn = 1 and @Dir = 'DESC' THEN Forename
WHEN @OrderByColumn = 2 and @Dir = 'DESC' THEN Surname
END) DESC
另一个例子:
SELECT * FROM dbo.Employee
ORDER BY
CASE WHEN Gender='Male' THEN EmployeeName END Desc,
CASE WHEN Gender='Female' THEN Country END ASC
更多细节.. 。http://codechef4u.com/post/2015/04/07/order-by-clause-with-case-expressions-case-statement
请参阅[这篇文章](http://sqlperformance.com/2012/08/t-sql-queries/conditional-order-by)和[这也许是一个有趣的阅读](http:// blogs。 sqlsentry.com/aaronbertrand/sql-variant-use-case/)。 – 2014-09-20 14:44:32