在 Postgresql 中模拟 MySQL 的 ORDER BY FIELD()

2021-11-20 00:00:00 postgresql mysql

第一次尝试 PostgreSQL，来自 MySQL.在我们的 Rails 应用程序中，我们有几个使用 SQL 的位置，如下所示:

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:

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

没过多久就发现 PostgreSQL 不支持/不允许这样做.

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?

推荐答案

啊，gahooa 如此接近:

Ah, gahooa was so close:

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;

相关文章