mysql分组后筛选

本方姓名与对方姓名联系次数为count（），筛选出每个相同的本方姓名中count（）的值是前五名的，而且结果中不能用group_concat
,
下图是希望的结果，有人能帮忙提示一下吗

img

img

SET @num = 0;
SET @group_name='';
SELECT 
s.*
FROM 
stu s
INNER JOIN
(
SELECT 
id,
CASE WHEN @group_name=u_name
THEN @num := @num + 1 
ELSE @num:=1
END AS `index`,
@group_name:=u_name 
FROM `stu` 
ORDER BY t_count DESC 
) ss 
ON s.`id` = ss.id
WHERE ss.index < 6

img


select
 *
from (
    select 
        a.name1, 
        a.name2,
        a.num,
        @F := case when @B = a.name1 then @F + 1
               else 1
        end as rank_no,
        @B := a.name1
    from (select * from test_demo order by name1,num desc) a,
    (select @F := 0,@B :='') b    
) a
where rank_no <= 5 ;

备注：把*换成想要展现的字段