Question

我在我的数据库中有一个表格,其中载有足球结果,相关栏目是相应的栏目(按日期编号)、结果(即“W”、“D”或“L”目标。商定和管理人。

我要问:

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

这完全可行。然而,结果跨越了几个季节,我希望打破僵局,以反映这一点。我已尝试如下:

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;

友情链接