刚从Mysql换到PostgreSQL,对PostgreSQL的函数了解还有所欠缺。我们都知道Mysql根据字段自定义排序可以使用 order by field() 函数,对于PG数据库如何实现这种效果?
官方文档:https://www.postgresql.org/docs/9.3/static/functions-string.html
查看官方文档,发现以下字符串处理函数:
Function | Return Type | Description | Example | Result |
---|---|---|---|---|
position(substring in string) | int | Location of specified substring | position(‘om’ in ‘Thomas’) | 3 |
strpos(string, substring) | int | Location of specified substring (same as position(substring in string), but note the reversed argument order) | strpos(‘high’, ‘ig’) | 2 |
以上两个函数均可返回子字符串在一个字符串中的位置
因此可以利用这个函数来达到我们的目的==>返回和IN中条件顺序相同的查询结果
select * from tb where id in (111, 444, 333, 222) order by position(id::text in ‘111,222,333,444’);
或
select * from tb where id in (111, 444, 333, 222) order by strpos(‘111,222,333,444’, id::text);
以上两种方式均可达到我们的目的,需要注意的是position和strpos均是字符串处理函数,因此如果IN条件字段类型是数字类型,需要转换成字符串。使用 cast(id as VARCHAR) 或者 id::text 都可。
实际项目中使用需要多传一个IN条件参数拼接的字符串参数,如Java中:
List<Integer> ins = Arrays.asList(111, 222, 333, 444);
String ids = ins.stream().map(String::valueOf).collect(Collectors.joining(","));
// 111,222,333,444
如果各位有其他实现方式,请评论指出,谢谢!
2022年8月11日更新
使用上面 order by position 和 order by strpos 的方式发现是有问题的!!!
如:
order by position(id::text in ‘2,3,4,5,6,7,8,9,10,11,12,13,14,1’)
最终结果为:(2,3,4,5,6,7,8,9,1,10,11,12,13,14)
请重视!!!
这里给出一种解决办法:
-- MYSQL中写法
SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC
-- PostgreSQL替换写法
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;
参考链接:Simulating MySQL’s ORDER BY FIELD() in Postgresql
直接用函数也能达到目的
order by array_position(ARRAY [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 1], id);