表結構:
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條
uj5u.com熱心網友回復：
大家多幫忙~ 我先去吃個飯~uj5u.com熱心網友回復：
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
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/102447.html
標籤：MySQL