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_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.

正确答复：

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 1 else 0 end )

【回答】

MySQL没有窗口函数，这种分组后再对子集的运算就不好想，看起来数据量不大，读出来在外面计算要方便一些，延用上面的SQL，SPL写法如下：

	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：sql取数，取得两个球员在同一球队一起比赛的进球数据

A2：按照t，p1，p2分组统计两个球员在同一球队一起比赛时p1球员比p2球员进球多的场次，和p1球员比p2进球少的场次

A3：从A2中选出high>low的记录，即选出进球多的场次更多的球员

这个代码也容易嵌到应用程序中，具体可参考Java 如何调用 SPL 脚本