mysql language sql immutable_在Postgresql中模擬MySQL的ORDER BY FIELD（）

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中模擬這種行為，還是我們必須將整理到代碼中？

10 个解决方案

#1

50

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;

#2

18

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}"