mysql一对多查询最新一条数据

** 单表循环 嵌套连表 group_concent **

一、单表循环，代码最简单，最好理解，性能最低

$data = db("user") -> alias('u')
   ->join("user_images i","u.id=i.user_id","left")
   ->field("max(i.id) as id,u.id as user_id,u.group_id,u.mobile,u.nickname,u.avatar,u.level,u.profession,u.birthday,u.gender,u.signing,u.paidalbum,u.online,u.lng,u.lat,u.updatetime,u.location{$distanceStr}")
   ->where($where)
   ->group($groupBy)
   ->order($orderby)
   ->limit($start,$limit)
   ->select();
foreach ($data as &$info){
   if($info['id']){
      $info += db("user_images")->where(['id'=>$info['id']])->find();
   }else{
      $info +=['file'=>null,'like_users'=>null];
   }
}

二、嵌套连表 代码复杂 性能较低

SELECT i.*,u.id as user_id,u.group_id,u.mobile,u.nickname,u.avatar,u.level,u.profession,u.birthday,u.gender,u.signing,u.paidalbum,u.online,u.lng,u.lat,u.updatetime,u.location{$distanceStr}
FROM mm_user AS u LEFT JOIN (SELECT a.*  FROM mm_user_images AS a LEFT JOIN (SELECT MAX(id) AS id,file FROM mm_user_images GROUP BY user_id) AS b ON a.id = b.id WHERE a.id = b.id ) AS i ON i.user_id = u.id ORDER BY u.id desc;

三、联合查询+子查询 代码较简单，性能较高

select i.*,u.id as user_id FROM mm_user as u LEFT JOIN mm_user_images as i on i.user_id=u.id where i.id in (select max(id) as maxid from mm_user_images group by user_id) or i.id is null 
ORDER BY u.id desc

四、联合查询+子查询 将Max替换成了Group_concat性能最高， 注意group_concat最大长度 默认是1024个字符，超过会被截断，但是对于本条语句是没有影响的

select i.*,u.id as user_id FROM mm_user as u LEFT JOIN mm_user_images as i on i.user_id=u.id where i.id in (select substring_index(group_concat(id order by id desc),",",1) as maxid from mm_user_images group by user_id) or i.id is null ORDER BY u.id desc