sql - 用于复杂win查询的sql

Question

我有下表作为记录

TeamA   | TeamB      | Winner
--------+------------+---------
Lakers  | Sacramento | Lakers
Clipper | Rockets    | Rockets
Lakers  | Suns       | Suns

需要像这样转换成表格

Team   | Win | Loss | TotalMatches
Lakers |  1  |   1  |     2
........

我从工会开始但无法进步

Select 
    TeamA, TeamB
from 
    Records
union
Select 
     teamA, teamB

标签： sql

Answer 1

您可以先使用 bu 取消透视UNION ALL，然后应用条件聚合

SELECT team, SUM(CASE WHEN winner = team THEN 1 ELSE 0 END) AS Win,
             SUM(CASE WHEN winner != team THEN 1 ELSE 0 END) AS Loss,
             COUNT(*) AS TotalMatches
  FROM ( SELECT teamA AS team, Winner FROM Records
         UNION ALL 
         SELECT teamB, Winner FROM Records ) r
 GROUP BY team

Demo