TP5解决groupBy后不能使用orderBy进行排序 # 背景 有一个消息表，相同的人需要抽离出最新的一条数据显示在消息列表。  展示如下  使用传统sql ```sql SELECT `id`, `position_id`, `content`, `ambulance`, `from_id`, `from_name`, `avatar`, `to_id`, `create_time` FROM `cc_position_message` WHERE ( ( `to_id` = '2' OR `from_id` = 2 ) ) AND `cc_position_message`.`delete_time` IS NULL GROUP BY position_id ORDER BY create_time DESC LIMIT 0, 30 ``` <span style="color:red;">**得到结果并未按create_time倒序排列。**</span> # 解决办法 检索列表数据 ```sql SELECT * FROM ( SELECT `id`, `position_id`, `content`, `ambulance`, `from_id`, `from_name`, `avatar`, `to_id`, `create_time` FROM `cc_position_message` WHERE ( ( `to_id` = '2' OR `from_id` = 2 ) ) AND `cc_position_message`.`delete_time` IS NULL ORDER BY `create_time` DESC LIMIT 0, 30 ) AS t GROUP BY t.`position_id`; ``` 统计消息数量（是列表上的数量，不是有多少条消息） ```sql SELECT count( * ) FROM ( SELECT * FROM cc_position_message WHERE ( ( `to_id` = '2' OR `from_id` = 2 ) ) AND `cc_position_message`.`delete_time` IS NULL GROUP BY position_id ) t ```` 使用PT5子查询 ```php //使用子查询，防止groupby后不能使用orderby排序 $query = new Query(); $query->where(function ($query) use ($param) { $query->where('to_id', $param['userId'])->whereOr('from_id', $param['userId']); }); //使用子查询，防止groupby后不能使用orderby排序 $field = "id,position_id,content,ambulance,from_id,from_name,avatar,to_id,create_time"; $subsql = $model ->where($query) ->group('position_id') ->buildSql(); $exp['count'] = Db::table($subsql . ' as t')->count(); $subsql = $model ->where($query) ->order('create_time desc') ->page($page, $limit) ->field($field) ->buildSql(); $data = Db::table($subsql . ' as t')->group('t.position_id')->select(); ```