postgresql 自定义订单说明
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4088532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Custom ORDER BY Explanation
提问by Evil Elf
I found this some time ago and have been using it since; however, looking at it today, I realized that I do not fully understand why it works. Can someone shed some light on it for me?
我前段时间发现了这个，并且一直在使用它；然而，今天看着它，我意识到我不完全理解它为什么起作用。有人可以为我解释一下吗？
ORDER BY s.type!= 'Nails',
s.type!= 'Bolts',
s.type!= 'Washers',
s.type!= 'Screws',
s.type!= 'Staples',
s.type!= 'Nuts', ...
If I order by s.type, it orders alphabetically. If I use the example above it uses the same order as the line positions. What I don't understand is the use of !=. If I use = it appears in the opposite order. I cannot wrap my head around the concept of this.
如果我按 s.type 排序，它会按字母顺序排序。如果我使用上面的示例，它使用与行位置相同的顺序。我不明白的是 != 的使用。如果我使用 = 它以相反的顺序出现。我无法理解这个概念。
It would reason to me that using = in place of the !='s above would place Nails first in position, but it does not, it place it in the last. I guess my question is this: Why do i have to use !=, not = in this situation?
我有理由使用 = 代替上面的 !='s 会将 Nails 放在第一个位置，但事实并非如此，它将它放在最后一个。我想我的问题是：在这种情况下，为什么我必须使用 !=，而不是 =？
采纳答案by musiKk
I've never seen it but it seems to make sense.
我从未见过它，但它似乎是有道理的。
At first it orders by s.type != 'Nails'. This is falsefor every row that contains Nailsin the typecolumn. After that it is sorted by Bolts. Again for all columns that do contain Boltsas a typethis evaluates to false. And so on.
起初它由 订购s.type != 'Nails'。这是false一个包含每一行Nails的type列。之后按 排序Bolts。再次对于包含Boltsas 的所有列，typethis 评估为 false。等等。
A small test reveals that falseis ordered before true. So you have the following: First you get all rows with Nailson top because the according ORDER BYevaluated to falseand falsecomes first. The remaining rows are sorted by the second ORDER BYcriterion. And so on.
一个小测试显示false是之前订购的true。所以，你有以下几种：一是你得到所有行Nails之上，因为，根据ORDER BY评估，以false和false第一。其余行按第二个ORDER BY条件排序。等等。
type | != Nails | != Bolts | != Washers 'Nails' | false | true | true 'Bolts' | true | false | true 'Washers' | true | true | false
回答by Scott Bailey
Each expression gets evaluated as a bool and treated as 0 for false and 1 for true and sorted appropriately. Even though this works, the logic is hard to follow (and thus maintain). What I use is a function that finds a value's index in an array.
每个表达式都被评估为一个布尔值，并被视为 0 表示假，1 表示真，并适当排序。即使这有效，逻辑也很难遵循（并因此维护）。我使用的是在数组中查找值索引的函数。
ORDER BY idx(array['Nails','Bolts','Washers','Screws','Staples','Nuts'], s.type)
This is much easier to follow. Nails will be sorted first and nuts sorted last. You can see how to create the idx function in the Postgres snippets repository. http://wiki.postgresql.org/wiki/Array_Index
这更容易遵循。钉子会先排序，坚果会最后排序。您可以在 Postgres 代码段存储库中查看如何创建 idx 函数。 http://wiki.postgresql.org/wiki/Array_Index
回答by Daniil Ryzhkov
@Scott Baileysuggested great idea. But it can be even simpler (you don't have to create custom function) since PostgreSQL 9.5. Just use array_positionfunction:
@Scott Bailey提出了好主意。但从 PostgreSQL 9.5 开始，它甚至可以更简单（您不必创建自定义函数）。只需使用array_position功能：
ORDER BY array_position(array['Nails','Bolts','Washers','Screws','Staples','Nuts'], s.type)
回答by lifeiscontent
with array_position, it needs to have the same type that you're querying against.
使用 array_position，它需要与您查询的类型相同。
e.g:
例如：
select array_position(array['foo'::char,'bar','baz'::char], 'bar');
select array_position(array['foo'::char,'bar','baz'::char], 'baz'::char);
