mysql實現每組取前N條記錄的sql，以及後續的組資料量限制

select a.msg_id, a.com_id, a.data, a.ctime from sns_user_03.user_request_86 a where 5 > (select count(*) from sns_user_03.user_request_86 where uid=8880386 and com_id= a.app_id and msg_id > a.msg_id ) order by a.ctime;

 

 

上面的sql實現分組查詢，每組最多顯示5條記錄，按照時間排序

 

 

select @rank:=0;

select msg_id, app_id, data, ctime, rank from ( select a.msg_id, a.app_id, a.data, a.ctime, if(@appid!=a.app_id,@rank:=@rank+1,@rank:=@rank) as rank, @appid:=a.app_id from sns_user_03.user_request_86 a where 5 > (select count(*) from sns_user_03.user_request_86 where uid=8880386 and app_id = a.app_id and msg_id > a.msg_id ) and @rank <= 4 order by a.msg_id desc ) `temp` where rank >2 and rank <=4;

 

上面實現取第3到第4組，每組5條記錄，按時間排序

 

 

這隻是單純DB實現，在實際應用中，對於這樣耗時的DB操作應當盡量避免，最好將資料cache出來，在邏輯層做這種複雜操作