MySql通过in查询怎样排序问题

最新推荐文章于 2023-12-08 17:37:49 发布

weixin_33743703

最新推荐文章于 2023-12-08 17:37:49 发布

阅读量226

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/swingcoder/blog/665684

版权

2019独角兽企业重金招聘Python工程师标准>>>

mysql里通常用field in语句查询时，排序会使用field来排序，但事实上一般是要用in(...)里面的条件来排序的，比如按地址位置查询周边产品时，要根据in里面的条件排序，可以使用

方法一、order by substring_index方式进行排序。

SELECT groupon_id FROM csq_groupon_2_poi WHERE poi_id in (56736,56713,56669,56920,56666,56667,56668,56228,56689,56738,56691,56732,56018,56185,56195) order by substring_index('56736,56713,56669,56920,56666,56667,56668,56228,56689,56738,56691,56732,56018,56185,56195',poi_id,1)

SELECT groupon_id FROM csq_groupon_2_poi WHERE poi_id in (56736,56713,56669,56920,56666,56667,56668,56228,56689,56738,56691,56732,56018,56185,56195) order by  substring_index('56736,56713,56669,56920,56666,56667,56668,56228,56689,56738,56691,56732,56018,56185,56195',poi_id,1)

方法二、order by find_in_set

SELECT groupon_id,poi_id FROM csq_groupon_2_poi WHERE poi_id in (56736,56713,56669,56920,56666,56667,56668,56228,56689,56738,56691,56732,56018,56185,56195) order by FIND_IN_SET(poi_id,'56736,56713,56669,56920,56666,56667,56668,56228,56689,56738,56691,56732,56018,56185,56195')

SELECT groupon_id,poi_id FROM csq_groupon_2_poi WHERE poi_id in (56736,56713,56669,56920,56666,56667,56668,56228,56689,56738,56691,56732,56018,56185,56195) order by  FIND_IN_SET(poi_id,'56736,56713,56669,56920,56666,56667,56668,56228,56689,56738,56691,56732,56018,56185,56195')

转载于:https://my.oschina.net/swingcoder/blog/665684