Table structure:

Team table team_id, name

Answer against table team_id: the submitter id,

Target_team_id by attacker id


To query all teams attack other team up to five team. That is 1 has five teams, team 2 has five... Until the team 60

Example: team 1 attack team 10 times, 2 team 3 20 times,... Team 60 10 times.

Team 2 team 1 20 times,... Team 60 10

A total of 60 teams, each team before 5

CodePudding user response:

Everybody many help ~ I'll go get dinner

CodePudding user response:

 
The create table team (
Team_id varchar (20), 
The name varchar (20) 
); 
Insert into team VALUES (' 001 ', 'the rockets'); 
Insert into team VALUES (' 002 ', 'small teams'); 
Insert into team VALUES (' 003 ', 'pioneers'); 
Insert into team VALUES (' 004 ', 'the mavericks'); 
Insert into team VALUES (' 005 ', 'fire wolves'); 
Insert into team VALUES (' 006 ', 'benben teams'); 
Insert into team VALUES (' 007 ', 'hogs'); 

The 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'); 


Insert table tab_005 # group summary data query in 
The create table Tab_005 (
Select team_id target_team_id, count (1) the as CNT 
The from answer 
Group by team_id, target_team_id 
); 

# group take the top 5 ranking 
SELECT 
* 
The FROM 
Tab_005 as a 
WHERE 
The EXISTS (
SELECT 
COUNT (1) 
The FROM 
Tab_005 b 
WHERE 
B.t eam_id=a.t eam_id 
AND the biggest nt & gt; A.c nt 
HAVING 
COUNT (1) & lt; 5 
)