问题描述
考虑下面名为 Persons 的表格:
Consider the following table named Persons:
Key Name Type Date Pack 1 Pocoyo KIND 30-11-2011 1 2 Erik MAN 10-10-1980 1 3 Alinda VROUW 12-12-1991 1 4 Pingu KIND 11-12-2012 1 5 Elisia KIND 11-11-2010 1 6 Kees MAN 10-11-1984 2
现在我想按Pack、Type 和Date 对这个表进行排序,但我想要Type 要像 MAN、VROUW、KIND 一样排序,所以基本上想要的结果应该是:
Now I would like to sort this table on Pack, Type and Date, but I would like the Type to be sorted like MAN, VROUW, KIND, so basically the desired outcome should be like:
Key Name Type Date Pack 2 Erik MAN 10-10-1980 1 3 Alinda VROUW 12-12-1991 1 5 Elisia KIND 11-11-2010 1 1 Pocoyo KIND 30-11-2011 1 4 Pingu KIND 11-12-2012 1 6 Kees MAN 10-11-1984 2
如何创建此查询?
推荐答案
试用
SELECT * FROM Persons ORDER BY Pack, CASE Type WHEN 'MAN' THEN 1 WHEN 'VROUW' THEN 2 WHEN 'KIND' THEN 3 END, Date ASC
MSDN:案例 (Transact-SQL)
CASE 可用于任何允许有效的语句或子句中表达.例如,您可以在语句中使用 CASE,例如SELECT、UPDATE、DELETE 和 SET,以及在 select_list 等子句中,IN、WHERE、ORDER BY 和 HAVING.
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.