在SQL中按子句自定义排序? [英] Custom Sorting in SQL order by clause?

问题描述

这是我要解决的情况:

我有一个查询，可以返回一组记录.排序依据的字段可以有许多不同的值-为了回答这个问题，我们将说该值可以是A，B，C，D，E或Z

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-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.

例如，如果找到A C D，则结果应为

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

A
C
D

但是，如果找到A B D E Z，则应对结果进行排序:

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

Z
A
B
D
E

当前，查询如下:

SELECT NAME, SOME_OTHER_FIELDS FROM TABLE ORDER BY NAME

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

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专家，您能否为我提供一个查询，该查询将完成我想要的工作?

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

推荐答案

如何识别Z记录?是什么让它与众不同?了解这一点后，将其添加到您的ORDER BY子句中.

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

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

这样，只有Z记录将与第一顺序匹配，而所有其他记录将通过第二顺序排序(名称)进行排序.如果您确实不需要二阶排序，则可以将其排除.

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是字符串"Bob"，则您的查询可能是:

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

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

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

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

这篇关于在SQL中按子句自定义排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！