GROUP BY和ORDER BY同时使用的问题

要在FastAdmin框架中执行分组搜索并获取每个组中最新的数据，并按照分组出现次数最多进行排序限制，可以结合使用模型类和原生SQL查询来实现

// 执行原生SQL查询
$result = YourModel::alias('t1')
    ->join("
        (SELECT creator_id, MAX(created_at) AS latest_created_at
        FROM your_table
        GROUP BY creator_id) t2",
        't1.creator_id = t2.creator_id AND t1.created_at = t2.latest_created_at'
    )
    ->select();

展示代码：

$takelist = Take::alias('t1')
            ->join("
        (SELECT admin_id, MAX(createtime) AS latest_createtime, COUNT(id) AS count
        FROM xilu_xiluhouse_user_take_log
        WHERE rental_id = $rental_id
        GROUP BY admin_id ORDER BY count DESC
        ) t2", 't1.admin_id = t2.admin_id AND t1.createtime = t2.latest_createtime'
            )->limit(5)->select();

解析：

1.根据admin_id进行分组 

GROUP BY admin_id

2.分组之后取最新时间的一条数据

MAX(createtime)

3.计算出每个分组的数量

COUNT(id) AS count

4.根据分组数量的大小进行排序

ORDER BY count DESC

5.取出分组展示的条数

limit(5)