【问题】

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写法如下：

A1：sql取数，取得两个球员在同一球队一起比赛的进球数据

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

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

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