在 Postgresql 中模拟 MySQL 的 ORDER BY FIELD()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1309624/
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
Simulating MySQL's ORDER BY FIELD() in Postgresql
提问by Peer Allan
Just trying out PostgreSQL for the first time, coming from MySQL. In our Rails application we have a couple of locations with SQL like so:
第一次尝试 PostgreSQL，来自 MySQL。在我们的 Rails 应用程序中，我们有几个使用 SQL 的位置，如下所示：
SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC, name ASC
It didn't take long to discover that this is not supported/allowed in PostgreSQL.
很快就发现 PostgreSQL 不支持/不允许这样做。
Does anyone know how to simulate this behaviour in PostgreSQL or do we have to pull sorting out into the code?
有谁知道如何在 PostgreSQL 中模拟这种行为，或者我们是否必须在代码中进行排序？
回答by Greg Smith
Ah, gahooa was so close:
啊，gahooa 是如此接近：
SELECT * FROM currency_codes
ORDER BY
CASE
WHEN code='USD' THEN 1
WHEN code='CAD' THEN 2
WHEN code='AUD' THEN 3
WHEN code='BBD' THEN 4
WHEN code='EUR' THEN 5
WHEN code='GBP' THEN 6
ELSE 7
END,name;
回答by ilgam
sort in mysql:
在mysql中排序：
> ids = [11,31,29]
=> [11, 31, 29]
> User.where(id: ids).order("field(id, #{ids.join(',')})")
in postgres:
在 postgres 中：
def self.order_by_ids(ids)
order_by = ["CASE"]
ids.each_with_index do |id, index|
order_by << "WHEN id='#{id}' THEN #{index}"
end
order_by << "END"
order(order_by.join(" "))
end
User.where(id: [3,2,1]).order_by_ids([3,2,1]).map(&:id)
#=> [3,2,1]
回答by pilcrow
Update, fleshing out terrific suggestion by @Tometzky.
更新，充实了@Tometzky 的绝妙建议。
This ought to give you a MySQL FIELD()-alike function under pg 8.4:
这应该会FIELD()在 pg 8.4 下为您提供一个类似 MySQL 的函数：
-- SELECT FIELD(varnames, 'foo', 'bar', 'baz')
CREATE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS numeric AS $$
SELECT
COALESCE(
( SELECT i FROM generate_subscripts(, 1) gs(i)
WHERE [i] = ),
0);
$$ LANGUAGE SQL STABLE
Mea culpa, but I cannot verify the above on 8.4 right now; however, I can work backwards to a "morally" equivalent version that works on the 8.1 instance in front of me:
Mea culpa，但我现在无法在 8.4 上验证上述内容；但是，我可以倒退到适用于我面前的 8.1 实例的“道德”等效版本：
-- SELECT FIELD(varname, ARRAY['foo', 'bar', 'baz'])
CREATE OR REPLACE FUNCTION field(anyelement, anyarray) RETURNS numeric AS $$
SELECT
COALESCE((SELECT i
FROM generate_series(1, array_upper(, 1)) gs(i)
WHERE [i] = ),
0);
$$ LANGUAGE SQL STABLE
More awkwardly, you still can portably use a (possibly derived) table of currency code rankings, like so:
更尴尬的是，您仍然可以便携地使用（可能派生的）货币代码排名表，如下所示：
pg=> select cc.* from currency_codes cc
left join
(select 'GBP' as code, 0 as rank union all
select 'EUR', 1 union all
select 'BBD', 2 union all
select 'AUD', 3 union all
select 'CAD', 4 union all
select 'USD', 5) cc_weights
on cc.code = cc_weights.code
order by rank desc, name asc;
code | name
------+---------------------------
USD | USA bits
CAD | Canadian maple tokens
AUD | Australian diwallarangoos
BBD | Barbadian tridents
EUR | Euro chits
GBP | British haypennies
(6 rows)
回答by Tometzky
This is I think the simplest way:
这是我认为最简单的方法：
create temporary table test (id serial, field text);
insert into test(field) values
('GBP'), ('EUR'), ('BBD'), ('AUD'), ('CAD'), ('USD'),
('GBP'), ('EUR'), ('BBD'), ('AUD'), ('CAD'), ('USD');
select * from test
order by field!='GBP', field!='EUR', field!='BBD',
field!='AUD', field!='CAD', field!='USD';
id | field
----+-------
1 | GBP
7 | GBP
2 | EUR
8 | EUR
3 | BBD
9 | BBD
4 | AUD
10 | AUD
5 | CAD
11 | CAD
6 | USD
12 | USD
(12 rows)
In PostgreSQL 8.4 you can also use a function with variable number of arguments(variadic function) to port fieldfunction.
在 PostgreSQL 8.4 中，您还可以使用具有可变数量参数的函数（可变参数函数）来移植field函数。
回答by jc.
Actually the version for postgres 8.1 as another advantage.
实际上 postgres 8.1 的版本是另一个优势。
When calling a postgres function you cannot pass more than 100 parameters to it, so your ordering can be done at maximum on 99 elements.
调用 postgres 函数时，您不能向它传递超过 100 个参数，因此您最多可以对 99 个元素进行排序。
Using the function using an array as second argument instead of having a variadic argument just remove this limit.
使用使用数组作为第二个参数而不是可变参数的函数只需删除此限制。
回答by gahooa
You can do this...
你可以这样做...
SELECT
..., code
FROM
tablename
ORDER BY
CASE
WHEN code='GBP' THEN 1
WHEN code='EUR' THEN 2
WHEN code='BBD' THEN 3
ELSE 4
END
But why are you hardcoding these into the query -- wouldn't a supporting table be more appropriate?
但是为什么要将这些硬编码到查询中——支持表不是更合适吗？
--
——
Edit: flipped it around as per comments
编辑：根据评论翻转它
回答by Craig Ringer
Just define the FIELDfunction and use it. It's easy enough to implement. The following should work in 8.4, as it has unnestand window functions like row_number:
只需定义FIELD函数并使用它。这很容易实现。以下应该在 8.4 中工作，因为它具有unnest和窗口功能，例如row_number：
CREATE OR REPLACE FUNCTION field(text, VARIADIC text[]) RETURNS bigint AS $$
SELECT n FROM (
SELECT row_number() OVER () AS n, x FROM unnest() x
) numbered WHERE numbered.x = ;
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;
You can also define another copy with the signature:
您还可以使用签名定义另一个副本：
CREATE OR REPLACE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS bigint AS $$
and the same body if you want to support field()for any data type.
如果您想支持field()任何数据类型，则使用相同的主体。
回答by Deepak Mahakale
Create a migration with this function
使用此功能创建迁移
CREATE OR REPLACE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS bigint AS $$
SELECT n FROM (
SELECT row_number() OVER () AS n, x FROM unnest() x)
numbered WHERE numbered.x = ;
$$ LANGUAGE SQL IMMUTABLE STRICT;
Then just do this
然后就这样做
sequence = [2,4,1,5]
Model.order("field(id,#{sequence.join(',')})")
voila!
瞧！
回答by 0xF4D3C0D3
SELECT * FROM (VALUES ('foo'), ('bar'), ('baz'), ('egg'), ('lol')) t1(name)
ORDER BY ARRAY_POSITION(ARRAY['foo', 'baz', 'egg', 'bar'], name)
How about this? above one fetch as below:
这个怎么样？以上一次取货如下：
foo
baz
egg
bar
lol
as you already get it, if an element isn't in the array then it goes to the back.
正如你已经得到的那样，如果一个元素不在数组中，那么它就会回到后面。
回答by Sorin Mocanu
If you'll run this often, add a new column and a pre-insert/update trigger. Then you set the value in the new column based on this trigger and order by this field. You can even add an index on this field.
如果您经常运行它，请添加一个新列和一个预插入/更新触发器。然后根据此触发器和此字段的顺序在新列中设置值。您甚至可以在该字段上添加索引。
