说明定制订单

我在不久前发现了它，并且一直在使用它; 然而，今天看到它，我意识到我并不完全理解它为什么能工作。有人能解释一下吗？

ORDER BY  s.type!= 'Nails',
s.type!= 'Bolts',
s.type!= 'Washers',
s.type!= 'Screws',
s.type!= 'Staples',
s.type!= 'Nuts', ...

如果我按 s.type 排序，它会按字母顺序排序。如果我使用上面的例子，它使用与行位置相同的顺序。我不明白的是用途！=.如果我使用 = 它以相反的顺序出现。我无法理解这个概念。

对我来说，使用 = 代替！= 的上面会把钉子放在第一个位置，但它不，它放在最后。我想我的问题是: 为什么我必须使用！在这种情况下？

31539 次浏览
小开
最佳答案

I've never seen it but it seems to make sense.

At first it orders by s.type != 'Nails'. This is false for every row that contains Nails in the type column. After that it is sorted by Bolts. Again for all columns that do contain Bolts as a type this evaluates to false. And so on.

A small test reveals that false is ordered before true. So you have the following: First you get all rows with Nails on top because the according ORDER BY evaluated to false and false comes first. The remaining rows are sorted by the second ORDER BY criterion. And so on.

type     | != Nails | != Bolts | != Washers
'Nails'   | false    | true     | true
'Bolts'   | true     | false    | true
'Washers' | true     | true     | false
小开

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.

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

小开

@Scott Bailey suggested great idea. But it can be even simpler (you don't have to create custom function) since PostgreSQL 9.5. Just use array_position function:

ORDER BY array_position(array['Nails','Bolts','Washers','Screws','Staples','Nuts'], s.type)
小开

with array_position, it needs to have the same type that you're querying against.

e.g:

select array_position(array['foo'::char,'bar','baz'::char], 'bar');
select array_position(array['foo'::char,'bar','baz'::char], 'baz'::char);
小开

This is to sort by a given list of values.

Since Postgres 9.4, a clean solution is to LEFT JOIN to an unnested array WITH ORDINALITY and ORDER BY the resulting ordinality number:

SELECT ...
FROM   ...
LEFT   JOIN unnest ('{Nails,Bolts,Washers,Screws,Staples,Nuts}'::text[]) WITH ORDINALITY sort(type, ord) USING (type)
ORDER  BY sort.ord ...

fiddle

See:


提交答案