如何应用非标准 SQL 列排序顺序?
考虑下面名为 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
一样排序,所以基本上期望的结果应该就像:
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
我怎样才能创建这个查询?
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
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
How can I create this query?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
尝试
MSDN:CASE (Transact-SQL)
Try out
MSDN: CASE (Transact-SQL)
我倾向于创建一个新的参考数据表,例如
PersonType
来保存“Type”列的预期值及其相对排序优先级:并在此表上执行左外连接以确定针对 Person 的查询结果的排序顺序:
I would be inclined to create a new reference data table, say
PersonType
to hold the expected values of the "Type" column and their relative sort priority:And do a left outer join onto this table to determine the sort order for the results of the query against Persons:
我认为,由于您的评论,您的非标准订单是由于枚举中项目的顺序造成的。如果是这种情况,请考虑在数据库中存储该值,而不是枚举的名称。让您的 DAL 在读/写数据库时转换枚举。例如,今天在 EntityFramework 中,我们可以编写一个小的包装类,它成为枚举列的类型,并定义包装器和枚举之间的等效和隐式转换。有关枚举和 EF 的更多信息,请参阅此答案。在 2011 年 6 月 CTP< /a> EF 直接添加对此翻译的支持。
I assume, because of your comments, your non-standard order is due to the order of the items in the enum. If that's the case, consider storing the value, not the name of the enum in the database. Let your DAL convert the enum when reading/writing to the DB. For example, today in EntityFramework we can write a small wrapper class that becomes the type of the enum column and define equivalence and implicit conversions between the wrapper and the enum. See this answer for more information on enums and EF. In the June 2011 CTP EF adds support for this translation directly.