【Question】

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_string($Messiinthiscase)."'

AND S2.Player <>'".mysql_real_escape_string($Messiinthiscase)."';

Mytable is like:

Player | Team | Game | Goals

-------------------------------

Messi  A  G1  2

Neymar  A  G1  0

Messi  A  G2  2

Neymar  A  G2  3

Messi  A  G3  4

Neymar  A  G3  1

But I don't know how to implement a COUNT to compare both players. Probably it is a stupid question with an easy answer but I've been hours working on it and nothing comes to my mind.

A correct solution:

SELECT

S1.Team,

S1.Player,

S2.Player,

sum(case when s1.goals > s2.goals then 1 else 0 end) as Player1Higher,

sum(case when s1.goals < s2.goals then 1 else 0 end) as Player1Lower

FROM

Mytable S1

INNER JOIN Mytable S2

ON S1.Team = S2.Team

AND S1.Game = S2.Game

AND NOT S1.Player = S2.Player

WHERE

S1.Player LIKE'".mysql_real_escape_string($Messiinthiscase)."'

GROUP BY

S1.Team,

S1.Player,

S2.Player

HAVING

sum(case when s1.goals>s2.goals then 1 else 0 end) >sum(case when s1.goals < s2.goals then 1else 0 end)

 

【Answer】

MySQL will handle your question in a very roundabout way because it doesn’t have the window function for dealing with data in each of the subsets following a group operation. Suppose you have a relatively small volume of source data, I retrieve it out from the database to process in SPL (Structured Process Language). The retrieval step will be written based on the above SQL. Here’s the SPL script:

	A
1	$SELECT S1.Player   p1,S1.Goals g1,S1.Team t,S2.Player p2,S2.Goals g2  FROM Mytable S1 INNER JOIN Mytable S2  ON S1.Team = S2.Team AND S1.Game=S2.Game AND   S1.Player <> S2.Player
2	=A1.groups(t,p1,p2;count(g1>g2):high,   count(g1<g2):low)
3	=A2.select(high>low)

A1: The SQL-like statement gets the goal-scoring records where the two players shared the same team and game.

A2: Group A1’s table by t, p1, and p2 (team,player1 and player2) and, for each group count the number of times player 1 scored more goals than player 2 in each game and those player 1 scores less goals than player 2 in each game.

A3: Find the records where high is greater than low, that is, the player who scored more goals in more games than the other.

An SPL script is easily to be embedded into a Java program. To learn the details, refer to How to Call an SPL Script in Java.