mysql - 如何创建多个MySQL计数/分组依据/子查询？

Question

我的数据库中有一个包含足球比赛结果的表格，相关列是 matchNumber(基于日期的数字)、结果(“W”、“D”或“L”)goalsFor、goalsAg 和 manager。

我有这个查询:

SELECT COUNT(*) AS P,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W") AS W,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D") AS D,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "L") AS L,  
SUM(GoalsFor) AS F,  
SUM(GoalsAg) AS A,  
ROUND((SELECT SUM((((SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W") * 2) +(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D")))/((SELECT COUNT(*) FROM match_results WHERE manager = 13)*2)*100),2) AS pct  
FROM match_results WHERE manager = 13;

返回以下结果:

P   W  D  L  F   A   Pct  
213 92 49 72 296 247 54.69

这非常有效。然而，结果跨越了多个季节，我想分解查询的输出以反射(reflect)这一点。我尝试过以下方法:

SELECT LEFT(matchNumber,4) AS Season,  
COUNT(*) AS P,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W") AS W,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D") AS D,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "L") AS L,  
SUM(GoalsFor) AS F,  
SUM(GoalsAg) AS A,  
ROUND((SELECT SUM((((SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W") * 2) +(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D")))/((SELECT COUNT(*) FROM match_results WHERE manager = 13)*2)*100),2) AS pct  
FROM match_results WHERE manager = 13  
GROUP BY Season;

此查询的结果如下图所示:

http://www.kidderminsterharriers.com/images/query2.jpg

如您所见，“季节”、“P”、“F”和“A”列给出了我想要的结果，但其余列正在重复总计。因此，我的问题是如何调整我的查询，以便“W”、“D”和“L”列也为我提供输出每一行中的逐年总计，而不是总计？

Answer 1

问题是您的三个子查询没有按季节限制它们的数量。尝试将其更改为:

SELECT @season:=LEFT(matchNumber,4) AS Season,  
COUNT(*) AS P,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W" AND LEFT(matchNumber,4)=@season) AS W,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D" AND LEFT(matchNumber,4)=@season) AS D,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "L" AND LEFT(matchNumber,4)=@season) AS L,  
SUM(GoalsFor) AS F,  
SUM(GoalsAg) AS A,  
ROUND((SELECT SUM((((SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W") * 2) +(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D")))/((SELECT COUNT(*) FROM match_results WHERE manager = 13)*2)*100),2) AS pct  
FROM match_results WHERE manager = 13  
GROUP BY Season;