Postgresql获取球员的总比赛数

Question

我得到了以下Postgres查询：

SELECT p_id as player_id, name as player_name
FROM Players
LEFT OUTER JOIN matches
ON Players.p_id = matches.player1 or Players.p_id = matches.player2
;

并返回以下内容

 player_id |    player_name
-----------+-------------------
         1 | Twilight Sparkle
         1 | Twilight Sparkle
         2 | Fluttershy
         3 | Applejack
         3 | Applejack
         4 | Pinkie Pie
         5 | "Rarity
         5 | "Rarity
         6 | Rainbow Dash
         7 | Princess Celestia
         7 | Princess Celestia
         8 | Princess Luna

如何才能得到一个包含唯一p_id的表，其中包含每个p_id的名称和p_id所在的总行？

player_id |    player_name     |  total_matches
-----------+-------------------+------
         1 | Twilight Sparkle  | 2
         2 | Fluttershy        | 1
         3 | Applejack         | 1
         4 | Pinkie Pie        | 1
         5 | "Rarity           | 2
         6 | Rainbow Dash      | 1
         7 | Princess Celestia | 2
         8 | Princess Luna     | 1

Answer 1

您可以使用group子句来实现：

SELECT p_id as player_id, name as player_name, count(*) as total_matches
FROM Players
LEFT OUTER JOIN matches
ON Players.p_id = matches.player1 or Players.p_id = matches.player2
GROUP BY name
;