选择查询的MySQL

Question

我有六列的表格如下
Home_Team | Home_Score | Away_Score | Away_Team | Home_Result | Away_Result

我要选择双赢的HOME_TEAM记录的输出，洛斯&领带比赛。

但是，我使用了以下查询，而不是计算每个类别中所有匹配项的计数总数。

select a.Home_Team, 
if(a.Home_Result = 'Win', count(a.Home_Result), 0) as Win, 
if(b.Home_Result = 'Loss', count(b.Home_Result), 0) as Loss 
from nsfa.result_main_bck as a 
join nsfa.result_main_bck as b 
on a.Home_Team = b.Home_Team 
where a.Home_Result = 'Win' and b.Home_Result = 'Loss' 
Group by 1 

出了什么问题在此代码

我使用MySQL的方式。

问候

Answer 1

你不能使用这样的事情https://stackoverflow.com/a/13075582/285190不知道为什么你的加入表上的自我

所以沿着

select a.Home_Team, 
SUM(case when a.Home_Result = 'Win' then 1 else 0 end) as Win 
SUM(case when a.Home_Result = 'Loss' then 1 else 0 end) as Loss 
from nsfa.result_main_bck as a 
where a.Home_Result = 'Win' or a.Home_Result = 'Loss' 
Group By a.Home_Team 

（未测试）

Answer 2

if(a.Home_Result = 'Win', count(a.Home_Result), 0) as Win, 

这是一种错误的做法，而哟应该使用CASE表达与聚合函数一起像SUM()

sum(case when a.Home_Result = 'Win' then 1 else 0 end) as Win