mysql - 如何在mysql中选择输赢记录

Question

我有看起来像这样的桌面“游戏”

| team1_id | team2_id | team1_pts | team2_pts |

     1          3        101        117

     2          5        99         98

我想做这样的选择，从表“团队”中返回团队名称和获胜/失败记录

| team_name | win | loss 

   Boston     15   11        

   Miami      13   12       

我想出了如何获取仅包含胜利次数的表格，但不知道如何在同一个表格中获取胜利和失败。

Answer 1

您可以取消旋转并重新加入:

select t.name, sum(is_win) as num_wins, sum(is_loss) as num_losses
from ((select (case when team1_pts > team2_pts then team1_id else team2_id end) as team_id,
              1 as is_win, 0 as is_loss
       from game g
      ) union all
      (select (case when team1_pts < team2_pts then team1_id else team2_id end) as team_id,
              0, 1
       from game g
      )
     ) g join
     teams t
     on t.team_id = g.team_id
group by t.name