MySQL查询时，查询结果如何按照where in数组排序

MySQL查询时，查询结果如何按照where in数组排序

在查询中，MySQL默认是order by id asc排序的，但有时候需要按照where in 的数组顺序排序，比如where in的id查询数组为[922,106,104,103],正常情况查询出来的结果顺序为[103,104,106,922],这可能不是我们想要的结果，

我们期望查出来的结果顺序与where in的顺序一致，这里介绍两个方式：

1.使用find_in_set函数:

select * from table where id in (922,106,104,103) order by find_in_set(id,'922,106,104,103');

2.使用order by field

select * from table where id in (922,106,104,103) order by field(id,922,106,104,103);

下面是在tp5中的实现过程

 1 $path = '103-104-106-922';
 2 $arr = explode('-',$path);
 3 dump($arr);
 4 $new_arr = array_reverse($arr);
 5 dump($new_arr);
 6 $new_arr1 = implode(',',$new_arr);
 7 dump($new_arr1);
 8 $list = Db::name('member')
 9     ->where('id','in',$new_arr1)
10     ->where('type',2)
11     ->field('id,type')
12     //->order("find_in_set(id,$new_arr1)")
13     ->order("field(id,$new_arr1)")
14     ->select();
15 dump($list);die;

查询结果如下所示，可见实现了按照where in 数组顺序进行排序了

 1 array(4) {
 2   [0] => string(3) "103"
 3   [1] => string(3) "104"
 4   [2] => string(3) "106"
 5   [3] => string(3) "922"
 6 }
 7 array(4) {
 8   [0] => string(3) "922"
 9   [1] => string(3) "106"
10   [2] => string(3) "104"
11   [3] => string(3) "103"
12 }
13 string(15) "922,106,104,103"
14 array(4) {
15   [0] => array(2) {
16     ["id"] => int(922)
17     ["type"] => int(2)
18   }
19   [1] => array(2) {
20     ["id"] => int(106)
21     ["type"] => int(2)
22   }
23   [2] => array(2) {
24     ["id"] => int(104)
25     ["type"] => int(2)
26   }
27   [3] => array(2) {
28     ["id"] => int(103)
29     ["type"] => int(2)
30   }
31 }

 

转载于:https://www.cnblogs.com/cyfblogs/p/10813127.html