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