MS SQL Server与MySQL FIELD()函数有什么相似之处? [英] What is the MS SQL Server capability similar to the MySQL FIELD() function?

问题描述

MySQL提供了一个名为 FIELD()接受可变数量的参数.返回值是第一个参数在其余参数列表中的位置.换句话说:

MySQL provides a string function named FIELD() which accepts a variable number of arguments. The return value is the location of the first argument in the list of the remaining ones. In other words:

FIELD('d', 'a', 'b', 'c', 'd', 'e', 'f')

将返回4，因为'd'是第一个参数之后的第四个参数.

would return 4 since 'd' is the fourth argument following the first.

此功能提供了根据非常特定的顺序对查询结果进行排序的功能.对于我当前的应用程序，我需要管理四种状态:有效，已批准，已拒绝和已提交.但是，如果仅按状态列排序，我会感到结果列表的可用性降低了，因为被拒绝和处于活动状态的项目比已提交和已批准的项目更为重要.

This function provides the capability to sort a query's results based on a very specific ordering. For my current application there are four statuses that I need to manager: active, approved, rejected, and submitted. However, if I simply order by the status column, I feel the usability of the resulting list is lessened since rejected and active status items are more important than submitted and approved ones.

在MySQL中，我可以这样做:

In MySQL I could do this:

SELECT <stuff> FROM <table> WHERE <conditions> ORDER BY FIELD(status, 'rejected', 'active','submitted', 'approved')

，结果将按顺序排列，以使被拒绝的物品首先出现，然后是活动的物品，依此类推.因此，结果按对访客的重要性递减的顺序排列.

and the results would be ordered such that rejected items were first, followed by active ones, and so on. Thus, the results were ordered in decreasing levels of importance to the visitor.

我可以创建一个单独的表，该表枚举状态的重要性级别，然后按降序对查询进行排序，但是自从切换到MS SQL Server以来，这已经出现了几次，所以我想使用类似于MySQL的FIELD()的内置函数，询问是否可以避免使用多余的表和更复杂的查询.

I could create a separate table which enumerates this importance level for the statuses and then order the query by that in descending order, but this has come up for me a few times since switching to MS SQL Server so I thought I'd inquire as to whether or not I could avoid the extra table and the somewhat more complex queries using a built-in function similar to MySQL's FIELD().

谢谢你，
大卫·基斯(David Kees)

Thank you,
David Kees

推荐答案

使用 CASE表达式(SQL Server 2005 +):

Use a CASE expression (SQL Server 2005+):

ORDER BY CASE status
           WHEN 'active' THEN 1
           WHEN 'approved' THEN 2
           WHEN 'rejected' THEN 3
           WHEN 'submitted' THEN 4
           ELSE 5
         END

您可以使用此语法进行更复杂的评估(包括组合，或者如果需要使用LIKE)

You can use this syntax for more complex evaluation (including combinations, or if you need to use LIKE)

ORDER BY CASE 
           WHEN status LIKE 'active' THEN 1
           WHEN status LIKE 'approved' THEN 2
           WHEN status LIKE 'rejected' THEN 3
           WHEN status LIKE 'submitted' THEN 4
           ELSE 5
         END

这篇关于MS SQL Server与MySQL FIELD()函数有什么相似之处?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！