计算查询中的分钟数

I'm looking for a way to count the amount of minutes each player (or here just one) in my team has played. Simplified database table is as follows:

我正在寻找一种方法来计算每个球员(或者仅仅是其中一个)在我的队伍中的上场时间。简化数据库表如下:

matchid    action    minute    player
-------------------------------------
1          subbedin  30        Pele 
2          starter             Pele
2          subbedout 50        Pele
3          subbedin  70        Pele
3          red       80        Pele
4          starter             Pele

The query I have right now for the other stats:

我现在对其他统计数据的查询:

$query = mysql_query("SELECT *,
  SUM(CASE WHEN action = 'starter' OR action = 'subbedin' THEN 1 ELSE 0 END) AS games,
  SUM(CASE WHEN action = 'goal' OR action = 'pengoal' THEN 1 ELSE 0 END) AS goals,
  SUM(CASE WHEN action = 'yellow' THEN 1 ELSE 0 END) AS yellows,
  SUM(CASE WHEN action = 'red' THEN 1 ELSE 0 END) AS reds,
  // MINS GOES HERE
FROM league2012
GROUP BY player");

For every matchid the basic calculation is

对于每个matchid，基本的计算是。

( 90 OR subbedout OR red ) - ( starter OR subbedin )

For example in match 2

例如在匹配2中

subbedout (50) - starter (0) = 50

In the end the table should looke like this:

最后，桌子应该是这样的:

player    minutes    goals, cards, etc.
---------------------------------------
Pele      210        ...

I've been going through tutorials for the past hour and can't seem to figure out how to do it.

在过去的一小时里，我一直在学习各种教程，但似乎不知道该怎么做。

2 个解决方案

#1

  sum
    (
      case action
        when 'subbedin'  then 90 - minute
        when 'starter'   then 90
        when 'subbedout' then minute - 90
        when 'red'       then minute - 90
      end
    ) as minutes

#2

I would first calculated minutes played by every player for every match, add them up to obtain the totals per player. To combine the obtained results with the other stats you are calculating, I can see no other way than to do the other stats in the same way, i.e. first per player & match, then per player. Here's what I mean:

我将首先计算出每个球员在每场比赛中的上场时间，然后把他们加起来，得到每个球员的总上场时间。要将获得的结果与您正在计算的其他统计数据结合起来，我只能以同样的方式进行其他统计，即首先是每个球员和比赛，然后是每个球员。我的意思:

SELECT
  player,
  SUM(games) AS games,
  SUM(goals) AS goals,
  SUM(yellows) AS yellows,
  SUM(reds) AS reds,
  SUM(minutesplayed) AS minutesplayed
FROM (
  SELECT
    player,
    matchid,
    SUM(CASE WHEN action IN ('starter', 'subbedin') THEN 1 ELSE 0 END) AS games,
    SUM(CASE WHEN action IN ('goal', 'pengoal') THEN 1 ELSE 0 END) AS goals,
    SUM(CASE WHEN action = 'yellow' THEN 1 ELSE 0 END) AS yellows,
    SUM(CASE WHEN action = 'red' THEN 1 ELSE 0 END) AS reds,
    IFNULL(SUM(CASE WHEN action IN ('subbedout', 'red') THEN minute END), 90)
    - IFNULL(SUM(CASE WHEN action = ('subbedin') THEN minute END), 0) AS minutesplayed
  FROM league2012
  GROUP BY
    player,
    matchid
) s
GROUP BY
  player