mysql- 分组后对子集运算

【问题】

I'm trying to compare two soccer players who played in the same team and I'm trying to know who scored more goals in each game they played together and COUNT the number of times a player scored more goals than the other in each game. For example:

·         Game 1: Messi 2 goals - Neymar 0 goals

·         Game 2: Messi 2 goals - Neymar 3 goals

·         Game 3: Messi 4 goals - Neymar 1 goal

The final result should be Messi = 2 , because he scored more goals in 2 games.

I have the next query to find the players who share the same team and game with my chosen player (Messi in this example):

SELECT S1.Team, S1.Game, S1.Player, S2.Team, S2.Game, S2.Player
FROM Mytable S1
INNER JOIN Mytable S2 ON S1.Team = S2.Team AND
S1.Game= S2.Game AND
S1.Player LIKE '".mysql_real_escape_