PostgreSQL跨两层表关联实现球队参赛及胜平负场数统计查询

基础环境说明 #

• 开发环境为PostgreSQL，业务涉及matches（赛事对阵表）、teams（球队信息表）、results（赛事比分表）三张数据表
• 已搭建可复现问题的测试环境，各表结构及示例数据如下：
  1. teams 球队信息表

  id_team |      club      | shorthand
  ---------+----------------+-----------
         1 | Vinica         | VIN
         2 | Kolpa          | KOL
         3 | Crnomelj       | CRN
         4 | Klub Študentov | KBŠ
         5 | Zilje          | ZLJ
         6 | Preloka        | PRK
  

  2. matches 赛事对阵表

  id_match | id_home | id_away |      date_time
  ----------+---------+---------+---------------------
          1 |       1 |       2 | 2018-11-03 16:30:00
          2 |       1 |       3 | 2019-11-03 16:30:00
          3 |       2 |       3 | 2019-11-03 16:30:00
          4 |       3 |       1 | 2020-11-03 16:30:00
  

  3. results 赛事比分表

  id_match | home | away
  ----------+------+------
          1 |    5 |    3
          2 |    2 |    0
          3 |    0 |    0
          4 |    1 |    3
  

当前已开发完成的逻辑 #

• 已编写可统计各球队总参赛场数的SQL语句，代码如下：

select t.club as team_name, count(*) as total_number_of_matches
from matches m cross join lateral
     (values (m.id_home), (m.id_away)
     ) v(id_team) join
     teams t
     on t.id_team = v.id_team
group by t.club;

• 该SQL当前输出结果为：

team_name | total_number_of_matches
----------+--------------------------
Vinica    | 3
Kolpa     | 2
Crnomelj  | 3

最终期望实现效果 #

需在现有统计结果基础上，补充各球队的胜场数、平局数、负场数三个统计维度，目标输出结果如下：

team_name | total_number_of_matches | matches_won | matches_draw | matches_lost
----------+-------------------------+-------------+--------------+--------------
Vinica    | 3                       | 3           | 0            | 0
Kolpa     | 2                       | 0           | 1            | 1
Crnomelj  | 3                       | 0           | 1            | 2

当前开发卡点 #

无法关联results表存储的比分数据，结合现有参赛场数的统计逻辑完成胜平负类别的计数计算。

内容的提问来源于stack exchange，提问作者J86