开发笔记

  • 首页
  • 工具箱
三味线的博客
  1. 首页
  2. SQL
  3. 正文

PGSQL查询结果按IN顺序排序

2021-02-09 2551点热度 9人点赞 0条评论

一个使用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
标签: pgsql
最后更新:2021-02-09

三味线

不吃咸鱼的喵

点赞
< 上一篇

文章评论

razz evil exclaim smile redface biggrin eek confused idea lol mad twisted rolleyes wink cool arrow neutral cry mrgreen drooling persevering
取消回复

Captcha Code

COPYRIGHT © 2022 voidcat.cn. ALL RIGHTS RESERVED.

Theme Kratos Made By Seaton Jiang

蜀ICP备18010095号-1