mysql - SQL 对获奖者进行计数并包括 0 个获奖者

Question

我的查询有问题。我最近刚开始使用SQL。在下面的第一个表中，我想计算获胜最多的球队的数量，并包括零胜的球队。表名称以红色突出显示。

我希望我的查询结果看起来像我附加的第二张图片，或者像这样

Phenix  2
StarWar 1
Strack  1
Serious 0
Apolo   0
APTX    0
Poki    0

到目前为止我最好的查询是:

   SELECT t1.Team_Name
        , COUNT(winner) AS TotalWins
     FROM result r 
LEFT JOIN team_1 t1
       ON r.Team1_ID = t1.Team1_ID
 GROUP BY winner

          UNION

   SELECT t2.Team_Name
        , COUNT(distinct winner) AS TotalWins
     FROM result r 
LEFT JOIN team_2 t2
       ON r.Team2_ID = t2.Team2_ID
 GROUP BY Winner
 ORDER BY TotalWins DESC;

我得到的结果不包括零胜的球队，它只是为所有球队加 1。

Answer 1

在加入结果表之前先合并您的team表，然后您可以使用sum()函数来获取总胜利。

select t1.Team_Name, sum(case when coalesce(t2.battle_no, '') = '' then 0 else 1 end) as TotalWins
from (select Team_Name, team1_id as id from team_1
    union all 
    select Team_Name, team1_id as id from team_1) t1
left join result t2 on t2.Team2_ID = t1.team1_id or t2.Team1_ID = t1.team1_id