mysql 查询所有队伍攻击其他队伍最多的前5条 攻击数据很多 200分~

[qq_31296243]

表结构:

team 表 team_id , name

answer 攻击表 team_id : 提交者id ,

target_team_id 被攻击者id


要查询所有队伍攻击其他队伍最多的5个队伍. 也就是 队伍1 有5条 ,队伍2有5条....一直到队伍60

例: 队伍1 攻击 队伍2 10次,队伍3 20次,.... 队伍60 10次.

队伍2 攻击 队伍1 20次,...... 队伍60 10次

一共60个队伍, 取每个队伍前5条

[听雨停了]

create table team(
	team_id varchar(20), 
	name  varchar(20)
);
insert into team VALUES('001','火箭队');
insert into team VALUES('002','小花队');
insert into team VALUES('003','先锋队');
insert into team VALUES('004','小牛队');
insert into team VALUES('005','火狼队');
insert into team VALUES('006','笨笨队');
insert into team VALUES('007','野猪队');

create table answer(
	 team_id varchar(20),
	target_team_id varchar(20)
);
insert into answer VALUES('001','002');
insert into answer VALUES('001','003');
insert into answer VALUES('001','003');
insert into answer VALUES('001','004');
insert into answer VALUES('001','004');
insert into answer VALUES('001','005');
insert into answer VALUES('001','005');
insert into answer VALUES('001','006');
insert into answer VALUES('001','006');
insert into answer VALUES('001','006');
insert into answer VALUES('001','007');
insert into answer VALUES('001','007');
insert into answer VALUES('001','007');
insert into answer VALUES('001','007');
insert into answer VALUES('002','001');
insert into answer VALUES('002','003');
insert into answer VALUES('002','003');
insert into answer VALUES('002','004');
insert into answer VALUES('002','004');
insert into answer VALUES('002','005');
insert into answer VALUES('002','005');
insert into answer VALUES('002','006');
insert into answer VALUES('002','006');
insert into answer VALUES('002','006');
insert into answer VALUES('002','007');
insert into answer VALUES('002','007');
insert into answer VALUES('002','007');
insert into answer VALUES('002','007');


#查询分组汇总数据插入表tab_005中
create table Tab_005(
select team_id,target_team_id,count(1) as cnt 
from answer 
group by team_id,target_team_id
);

#分组取前5的排名
SELECT
    *
FROM
    tab_005 as a
WHERE
    EXISTS (
        SELECT
            COUNT(1)
        FROM
            tab_005 b
        WHERE
            b.team_id = a.team_id
        AND b.cnt > a.cnt
        HAVING
            COUNT(1) < 5
    )

[qq_31296243]

大家多帮忙~ 我先去吃个饭~