在Postgresql中模拟MySQL的ORDER BY FIELD（）

Question

刚从MySQL首次尝试PostgreSQL。在我们的Rails应用程序中，我们有几个使用SQL的位置，如下所示：

SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC, name ASC

很快就发现PostgreSQL不支持/不允许这样做。

有谁知道如何在PostgreSQL中模拟这种行为，还是我们必须将排序整理到代码中？

Answer 1

在mysql中排序：> ids = [11,31,29]=> [11, 31, 29]> User.where(id: ids).order("field(id, #{ids.join(',')})")在postgres中：def self.order_by_ids(ids)&nbsp; order_by = ["CASE"]&nbsp; ids.each_with_index do |id, index|&nbsp; &nbsp; order_by << "WHEN id='#{id}' THEN #{index}"&nbsp; end&nbsp; order_by << "END"&nbsp; order(order_by.join(" "))endUser.where(id: [3,2,1]).order_by_ids([3,2,1]).map(&:id)&nbsp;#=> [3,2,1]

Answer 2

通过@Tometzky充实了很棒的建议。这应该FIELD()在pg 8.4下为您提供一个类似于MySQL的函数：-- SELECT FIELD(varnames, 'foo', 'bar', 'baz')CREATE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS numeric AS $$&nbsp; SELECT&nbsp; &nbsp; COALESCE(&nbsp; &nbsp; &nbsp;( SELECT i FROM generate_subscripts($2, 1) gs(i)&nbsp; &nbsp; &nbsp; &nbsp;WHERE $2[i] = $1 ),&nbsp; &nbsp; &nbsp;0);$$ LANGUAGE SQL STABLEMea culpa，但我现在无法在8.4上验证上述内容；但是，我可以向后工作到在我前面的8.1实例上可用的“道德上”等效的版本：-- SELECT FIELD(varname, ARRAY['foo', 'bar', 'baz'])CREATE OR REPLACE FUNCTION field(anyelement, anyarray) RETURNS numeric AS $$&nbsp; SELECT&nbsp; &nbsp; COALESCE((SELECT i&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM generate_series(1, array_upper($2, 1)) gs(i)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE $2[i] = $1),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0);$$ LANGUAGE SQL STABLE更尴尬的是，您仍然可以方便地使用（可能派生的）货币代码排名表，如下所示：pg=> select cc.* from currency_codes cc&nbsp; &nbsp; &nbsp;left join&nbsp; &nbsp; &nbsp; &nbsp;(select 'GBP' as code, 0 as rank union all&nbsp; &nbsp; &nbsp; &nbsp; select 'EUR', 1 union all&nbsp; &nbsp; &nbsp; &nbsp; select 'BBD', 2 union all&nbsp; &nbsp; &nbsp; &nbsp; select 'AUD', 3 union all&nbsp; &nbsp; &nbsp; &nbsp; select 'CAD', 4 union all&nbsp; &nbsp; &nbsp; &nbsp; select 'USD', 5) cc_weights&nbsp; &nbsp; &nbsp;on cc.code = cc_weights.code&nbsp; &nbsp; &nbsp;order by rank desc, name asc;&nbsp;code |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;name------+---------------------------&nbsp;USD&nbsp; | USA bits&nbsp;CAD&nbsp; | Canadian maple tokens&nbsp;AUD&nbsp; | Australian diwallarangoos&nbsp;BBD&nbsp; | Barbadian tridents&nbsp;EUR&nbsp; | Euro chits&nbsp;GBP&nbsp; | British haypennies(6 rows)