mysql - 使用案例查找百分比 - SELECT CASE 子查询

Question

几乎是 MySQL 的初学者。目前正在通过 Lynda.com 学习。

我有一个包含下表的数据库

tblmatches - matchID, matchdate, matchtime, matchstatus, teamhomeID,    teamAwayID, matchscorehome, matchscoreaway, seasonID, competitonID
tblteam - teamID, teamName

我试图找出每支球队有多少场比赛，两支球队都取得了进球。

我使用以下语句来查找所进行的比赛数量 (p)、两队得分的比赛次数 (y)，但无法计算出如何获得百分比。

我按球队名称分组，并且必须同时使用主场和客场比赛

SELECT
teamName AS Team, 
Sum(P) AS P,
SUM(Y) AS Y
FROM(
    SELECT
    teamHomeID Team,
    1 P,
    CASE WHEN matchScoreHome AND matchScoreAway > 0 THEN 1 ELSE 0 END Y 
    FROM tblmatches WHERE matchstatus = 'FT' AND competitionID = 1         AND     seasonID = 2
  UNION ALL
  SELECT
   teamAwayID,
   1,
   CASE WHEN matchScoreHome AND matchScoreAway > 0 THEN 1 ELSE 0 END Y
  FROM tblmatches WHERE matchstatus = 'FT' AND competitionID = 1 AND      seasonID = 2
  ) as tab
 JOIN tblteam t ON tab.team=t.teamID
 GROUP BY Team
ORDER BY SUM(Y) DESC ;

任何帮助将不胜感激。

Answer 1

找到答案，因为仍在学习中，还没有意识到 mysql 可以工作，但从引号之间的内容开始。那时我意识到 SUM 必须位于子查询之外，通过谷歌进行了一些搜索，并知道要放入初始 SELECT

SELECT
teamName AS Team, 
Sum(P) AS P,
SUM(Y) AS Y,
(SUM(Y) * 100) / SUM(P) AS 'BTTS PCT'
FROM(
    SELECT
    teamHomeID Team,
    1 P,
    CASE
    WHEN matchScoreHome AND matchScoreAway > 0 THEN 1 ELSE 0 
   END Y 
  FROM tblmatches WHERE matchstatus = 'FT' AND competitionID = 1        AND seasonID = 2
   UNION ALL
   SELECT
   teamAwayID,
   1,
   CASE 
    WHEN matchScoreHome AND matchScoreAway > 0 THEN 1 ELSE 0 
    END Y
   FROM tblmatches WHERE matchstatus = 'FT' AND competitionID = 1 AND   seasonID = 2 
   ) as tab
  JOIN tblteam t ON tab.team=t.teamID
 GROUP BY Team
 ORDER BY SUM(Y) DESC ;