PGSQL查询结果按IN顺序排序

一个使用IN的查询

SELECT id,fullname FROM sys_user WHERE id IN(5,74,6,67)
// 结果
5 nancy
6 helen
67 chris
74 莎莉

使用 POSITION

SELECT id,fullname FROM sys_user WHERE id IN(5,74,6,67) ORDER BY POSITION(id::TEXT IN '5,74,6,67')
// 或者
SELECT id,fullname FROM sys_user WHERE fullname IN('nancy','莎莉','helen','chris') ORDER BY POSITION(fullname IN 'nancy,莎莉,helen,chris')
// 结果
5    nancy
74    莎莉
6    helen
67    chris

使用 ARRAY_POSITIONS

SELECT id,fullname FROM sys_user WHERE id IN(5,74,6,67) ORDER BY ARRAY_POSITIONS(ARRAY[5,74,6,67], id)
// 或者
SELECT id,fullname FROM sys_user WHERE fullname IN('nancy','莎莉','helen','chris') ORDER BY ARRAY_POSITIONS(ARRAY['nancy','莎莉','helen','chris'], fullname::TEXT)
// 结果
5    nancy
74    莎莉
6    helen
67    chris