1,分组排序的核心就是,先用order by分组,然后排序
/* 最新的消息,多少人,每个人多少条,采用分组排序 */
$result=pdo_fetchall("select r.* from (SELECT *, (@num:=IF(@group = openid, @num +1, IF(@group := openid, 1, 1))) row_number
FROM ".tablename($this->table)."
ORDER BY openid,Id desc) r where r.openid
in(select a.openid from (select t1.openid from (select * from ".tablename($this->table)." order by id desc) t1
where t1.openid=t1.saytoone group by t1.openid order by t1.id desc limit 0,".$people.") as a) and r.row_number<=".$num." order by r.Id;");
return $result;
第二种方法:case when then else end ,并且通过select变量赋值
SELECT *,(@num:=(case when @group=openid then @num+1 else 1 end)) as row_number,@group:=openid as groupOpenid FROM ims_mdkeji_chatsingle_room_private ORDER BY openid,Id desc
2,group by 在order之前执行,导致获取的是排序在前面的那一行数据
3,(@num:=IF(@group = openid, @num +1, IF(@group := openid, 1, 1))) row_number这一行数据,从左往右执行,注意顺序