在Postgresql中模拟MySQL的ORDER BY FIELD() [英] Simulating MySQL's ORDER BY FIELD() in Postgresql

问题描述

只需第一次尝试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;

这篇关于在Postgresql中模拟MySQL的ORDER BY FIELD()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！