SQL order by 子句中的自定义排序？

Custom Sorting in SQL order by clause?

提问by Zeke Hansell

Here is the situation that I am trying to solve:

这是我试图解决的情况：

I have a query that could return a set of records. The field being sorted by could have a number of different values - for the sake of this question we will say that the value could be A, B, C, D, E or Z

我有一个可以返回一组记录的查询。被排序的字段可能有许多不同的值 - 为了这个问题，我们会说值可能是 A、B、C、D、E 或 Z

Now depending on the results of the query, the sorting needs to behave as follows: If only A-E records are found then sorting them "naturally" is okay. But if a Z record is in the results, then it needs to be the first result in the query, but the rest of the records should be in "natural" sort order.

现在根据查询的结果，排序需要如下进行：如果只找到 AE 记录，那么“自然地”排序它们是可以的。但是如果结果中有 Z 记录，那么它需要是查询中的第一个结果，但其余的记录应该按“自然”排序。

For instance, if A C D are found, then the result should be

例如，如果找到 ACD，那么结果应该是

A
C
D

But if A B D E Z are found then the result should be sorted:

但是，如果找到 ABDEZ，则应对结果进行排序：

Z
A
B
D
E

Currently, the query looks like:

目前，查询如下所示：

SELECT NAME, SOME_OTHER_FIELDS FROM TABLE ORDER BY NAME

I know I can code a sort function to do what I want, but because of how I am using the results, I can't seem to use because the results are being handled by a third party library, to which I am just passing the SQL query. It is then processing the results, and there seems to be no hooks for me to sort the results and just pass the results to the library. It needs to do the SQL query itself, and I have no access to the source code of the library.

我知道我可以编写一个排序函数来做我想做的事，但是由于我使用结果的方式，我似乎无法使用，因为结果是由第三方库处理的，我只是将SQL 查询。然后它正在处理结果，似乎没有任何钩子可以让我对结果进行排序并将结果传递给库。它需要自己执行 SQL 查询，而我无法访问该库的源代码。

So for all of you SQL gurus out there, can you provide a query for me that will do what I want?

所以对于你们所有的 SQL 专家，你们能否为我提供一个可以做我想做的查询？

回答by Stephen Jennings

How do you identify the Z record? What sets it apart? Once you understand that, add it to your ORDER BY clause.

你如何识别Z记录？是什么让它与众不同？一旦你理解了这一点，就把它添加到你的 ORDER BY 子句中。

SELECT name, *
FROM [table]
WHERE (x)
ORDER BY
    (
     CASE
       WHEN (record matches Z) THEN 0
       ELSE 1
     END
    ),
    name

This way, only the Z record will match the first ordering, and all other records will be sorted by the second-order sort (name). You can exclude the second-order sort if you really don't need it.

这样，只有 Z 记录将匹配第一个排序，所有其他记录将按第二个排序（名称）排序。如果确实不需要，可以排除二阶排序。

For example, if Z is the character string 'Bob', then your query might be:

例如，如果 Z 是字符串“Bob”，那么您的查询可能是：

SELECT name, *
FROM [table]
WHERE (x)
ORDER BY
    (
     CASE
       WHEN name='Bob' THEN 0
       ELSE 1
     END
    ), name

My examples are for T-SQL, since you haven't mentioned which database you're using.

我的示例是针对 T-SQL 的，因为您没有提到您使用的是哪个数据库。

回答by Larry Lustig

There are a number of ways to solve this problem and the best solution depends on a number of factors that you don't discuss such as the nature of those A..Z values and what database product you're using.

有多种方法可以解决此问题，最佳解决方案取决于您未讨论的许多因素，例如这些 A..Z 值的性质以及您使用的数据库产品。

If you have only a single value that has to sort on top, you can ORDER BYan expression that maps that value to the lowest possible sort value (with CASEor IIFor IFEQ, depending on your database).

如果您只有一个必须排在最前面的值，您可以使用ORDER BY一个表达式将该值映射到可能的最低排序值（使用CASE或IIF或IFEQ，取决于您的数据库）。

If you have several different special sort values you could ORDER BYa more complicated expression or you could UNIONtogether several SELECTs, with one SELECTfor the default sorts and an extra SELECTfor each special value. The SELECTs would include a sort column.

如果您有几个不同的特殊排序值，您可以ORDER BY使用更复杂的表达式，或者您可以UNION将多个SELECTs组合在一起，其中一个SELECT用于默认排序，SELECT每个特殊值都有一个额外的。该SELECTs就包括排序列。

Finally, if you have quite a few values you can put the sort values into a separate table and JOINthat table into your query.

最后，如果您有相当多的值，您可以将排序值放入一个单独的表中，然后JOIN将该表放入您的查询中。

回答by Yahia

Not sure what DB you use - the following works for Oracle:

不确定您使用的是什么数据库 - 以下适用于 Oracle：

SELECT 
NAME, 
SOME_OTHER_FIELDS, 
DECODE (NAME, 'Z', '_', NAME ) SORTFIELD 
FROM TABLE 
ORDER BY DECODE (NAME, 'Z', '_', NAME ) ASC