CodeIgniter中如何自定义排序规则实现指定顺序排列查询结果

解决方案 #

你只需要新增一层自定义排序规则，优先把order值为0的记录排在最后，再对非0值按升序排列即可，修改后的代码如下：

$this->db->select('*');
$this->db->from('languages');
// 第一层排序：非0值前置，0值后置，第三个参数FALSE避免CI自动转义排序表达式
$this->db->order_by('`order` = 0', 'ASC', FALSE);
// 第二层排序：非0值按order字段升序排列
$this->db->order_by('`order`', 'ASC');
$query = $this->db->get();

原理说明 #

• MySQL中order = 0是布尔判断表达式，当order字段值为0时返回结果为1，非0时返回结果为0，按该表达式升序排序时，返回值更小的非0记录会自动排在0值记录前面
• 第二层排序仅作用于非0的记录，按照order字段本身的大小升序排列，刚好匹配你需要的1、1、2、2、3的顺序

如果需要兼容其他数据库，也可以替换为更通用的CASE WHEN写法：

$this->db->order_by("CASE WHEN `order` = 0 THEN 1 ELSE 0 END", "ASC", FALSE);
$this->db->order_by('`order`', "ASC");

两种写法最终返回的结果都和你预期的完全一致。

内容的提问来源于stack exchange，提问作者neel