mysql-计算查询中的分钟数

提问

我正在寻找一种方法来计算团队中每个球员(或这里只有一个)的上场时间.简化的数据库表如下:

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

我现在对其他统计信息的查询:

$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");

对于每个matchid,基本计算为

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

例如在比赛2中

subbedout (50) - starter (0) = 50

最后,该表应如下所示:

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

在过去的一个小时中,我一直在阅读教程,似乎无法弄清楚该如何做.

最佳答案

  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
评论