mysql 子集_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):

SELECTS1.Team, S1.Game, S1.Player, S2.Team, S2.Game, S2.Player

FROMMytable S1

INNER JOINMytable S2ONS1.Team = S2.TeamAND

S1.Game= S2.GameAND

S1.PlayerLIKE '".mysql_real_escape_string($Messiinthiscase)."'

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

Mytable is like:

Player | Team | Game | Goals

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

Messi      A      G12

Neymar     A      G10

Messi      A      G22

Neymar     A      G23

Messi      A      G34

Neymar     A      G31

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 whens1.goals > s2.goalsthen 1 else 0 end)asPlayer1Higher,

sum(case whens1.goals < s2.goalsthen 1 else 0 end)asPlayer1Lower

FROM

Mytable S1

INNER JOINMytable S2

ONS1.Team = S2.Team

ANDS1.Game = S2.Game

AND NOTS1.Player = S2.Player

WHERE

S1.PlayerLIKE '".mysql_real_escape_string($Messiinthiscase)."'

GROUP BY

S1.Team,

S1.Player,

S2.Player

HAVING

sum(case whens1.goals > s2.goalsthen 1 else 0 end) >sum(case whens1.goals < s2.goalsthen 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

3

=A2.select(high>low)

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

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

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

这个代码也容易嵌到应用程序中，具体可参考