时间戳

首页 > 解决方案 > SQL 获取单独表中相关实体的计数

sql - SQL 获取单独表中相关实体的计数

问题描述

我在 Postgresql 数据库中有一个类似于以下内容的架构：

图式

所以我有一个players表和两个连接表matches。

第一个连接表matches_players定义了参加给定比赛的球员。第二个，scorers定义在给定比赛中哪些球员进球。同一玩家可以有多行并在scorers表中匹配。每行表示一个进球，因此如果一名球员在一场比赛中打进 3 个进球，那么将有 3 行带有match_id和player_id。

我正在尝试编写一个查询，它将返回一个球员 ID 表以及每个球员的比赛次数和进球数。例如

player_id match_played 进球得分
1 4 2
2 0 0
3 2 0

作为对此的扩展，我希望能够通过match. 例如，仅针对特定日期之后的比赛获得上述结果。

此查询似乎可以完成简单案例的工作，但我不确定如何按匹配条件进行过滤，因为有 2 个连接路径到matches.

SELECT p.id player_id,
       COUNT(DISTINCT m.match_id) matches_played,
       COUNT(DISTINCT s.id) goals_scored
FROM players p
LEFT JOIN matches_players mp ON mp.player_id = p.id
LEFT JOIN scorers s ON s.player_id = p.id
GROUP BY p.id

（可能值得注意的是，出现在scorers和中的行之间没有限制matches_players。理论上，对于给定的比赛，玩家可以有 1 行或更多行，scorers但在 中没有该比赛的行matches_players。在这种情况下，他们将有 a matches_played= 0 和goals_scored= 1.

这是因为球队选择与记录比赛数据的流程不同。这是将来可能会解决的问题，但现在可以忍受）。

标签： sqlpostgresql

解决方案

您的要求的问题是获取玩家参加过的比赛。
为此，您需要考虑match_id两者中的 smatches_players并且scorers您可以在子查询中使用UNION.
然后你需要LEFT加入players子查询scorers和聚合：

SELECT p.id player_id,
       COUNT(DISTINCT m.match_id) matches_played,
       COUNT(DISTINCT s.id) goals_scored
FROM players p
LEFT JOIN (
  SELECT player_id, match_id FROM matches_players
  UNION
  SELECT player_id, match_id FROM scorers
) m ON m.player_id = p.id
LEFT JOIN scorers s ON s.player_id = p.id
GROUP BY p.id

推荐阅读