mysql - 进球的时间(范围)

我正在尝试创建一个mysql查询，该查询将显示球队在比赛中进球的时间。我有一个像这样设置的表：clubid（int）和minutes（tinyint）。我要实现的目标是：

1-15分钟31个进球

16-30分钟射门得分

31-45分钟36个进球

46-60分钟进球

61-75分钟48个进球

76-90分钟52个进球

首先，我想为联盟中的每个团队做到这一点，然后为每个团队分别做。这是我查询整个联盟的尝试，但不幸的是它没有用。

  SELECT *,count(minute) as summary
  CASE WHEN minute >= 1  AND minute <= 15 THEN  '1-15'
       WHEN minute >= 16 AND minute <= 30 THEN '16-30'
       WHEN minute >= 31 AND minute <= 45 THEN '31-45'
       WHEN minute >= 46 AND minute <= 60 THEN '46-60'
       WHEN minute >= 61 AND minute <= 75 THEN '61-75'
       WHEN minute >= 76 AND minute <= 90 THEN '76-90'
       WHEN minute > 90  AND THEN 90+
       ELSE 'no goals' END as range
  FROM teamgoals
  ORDER BY range asc

预先感谢您的任何帮助！

最佳答案

请尝试sqlFiddle

SELECT IFNULL(T1.summary,0) as summary,
       rangeDescription.`range`
  FROM (      SELECT '1-15' as `range`, 1 as rowOrder
        UNION SELECT '16-30'        , 2
        UNION SELECT '31-45'        , 3
        UNION SELECT '46-60'        , 4
        UNION SELECT '61-75'        , 5
        UNION SELECT '76-90'        , 6
        UNION SELECT '90+'          , 7
        )rangeDescription
  LEFT JOIN
        (SELECT count(minute) as summary,
          CASE WHEN minute >= 1  AND minute <= 15 THEN  '1-15'
               WHEN minute >= 16 AND minute <= 30 THEN '16-30'
               WHEN minute >= 31 AND minute <= 45 THEN '31-45'
               WHEN minute >= 46 AND minute <= 60 THEN '46-60'
               WHEN minute >= 61 AND minute <= 75 THEN '61-75'
               WHEN minute >= 76 AND minute <= 90 THEN '76-90'
               WHEN minute > 90   THEN '90+'
               ELSE 'no goals'
          END as `range`
        FROM
        teamgoals
        WHERE clubId = 1
         GROUP BY `range`
        )T1
   ON rangeDescription.`range` = T1.`range`
ORDER BY rangeDescription.rowOrder asc

关于mysql - 进球的时间(范围)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20849664/