如何应用非标准 SQL 列排序顺序? [英] How to apply non standard SQL column sort order?

查看:33
本文介绍了如何应用非标准 SQL 列排序顺序?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

考虑下面名为 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

现在我想按PackTypeDate 对这个表进行排序,但我想要Type 要像 MANVROUWKIND 一样排序,所以基本上想要的结果应该是:

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.

这篇关于如何应用非标准 SQL 列排序顺序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆