mysql - 如何只统计left join中的记录？

Question

我需要计算特定球员在特定比赛中进入特定球队的进球数。我有三个表:

1. 比赛:包含球队在特定比赛(轮次)中的所有比赛
2. Player 包含球员的详细信息，例如:姓名、姓氏、高度等...
3. Goal:包含该球员在一场比赛中的所有进球

这是我的查询:

$query = "SELECT
COUNT(*) AS goal_scored,
p.first_name AS first_name,
p.last_name AS last_name
FROM `match` m
LEFT JOIN goal g ON g.match_id = m.id
LEFT JOIN player p ON g.player_marker_id = p.id
WHERE m.round_id = :round_id
AND (m.home_team_id = :team_id OR m.away_team_id = :team_id)
GROUP BY p.id
ORDER BY goal_scored DESC
LIMIT 5";

在查询的第一部分，我尝试使用 goal 和 player 的连接来选择搜索球队的球员得分的所有进球。我过滤了与 competition.id 相对应的 round 的结果。

我期待在特定比赛中返回球队的最佳射手。

数据示例

匹配:

id | home_team_id | away_team_id | 
 1        95            121
 2       203            95
 3       95             122

目标:

id | player_marker_id | match_id | team_id | result  | type
 1         1005            1           95      1 - 0   1
 2         1005            1           95      1 - 0   2
 3         1005            2           95      2 - 1   1
 4         1006            3           95      1 - 0   1

玩家:

id    | first_name | surname 
1005    Benedetto    Ismael
1006    Ramon        Abila 

正如您在表 match 中看到的那样，我有三个记录，这意味着 95 队已经打了 3 场比赛。这支球队有两名球员，球员Benedetto进了2个球，那支球队的头号射手也是，预期结果应该是:

Benedetto
Ramon

但我得到的 goal_scored 是一个不正确的结果，例如 3 goal scored，这是错误的，因为 type 在这个范围内并不重要。 type 简单地告诉我是哪种类型的进球，特别是 1 是比赛开始的进球，2 是普通进球。一个目标可以有多种类型，最多 2 种。

此外，我还发现了另一个bug，不同的球员可以在同一场比赛中得分，但是goal_scored的结果是不正确的，因为我只需要计算特定球员的进球数而不是所有球员的进球数球队队员。

如何解决我的查询？

生产结果

SELECT * FROM `match` m 
LEFT JOIN goal g ON m.id = g.match_id AND g.player_marker_id = 68165 
WHERE m.round_id = 488 AND (m.home_team_id = 95 OR m.away_team_id = 95)

Answer 1

我不明白你的问题。为什么要有 left join？

根据我对您数据的解释，您只想查看 type = 1 的目标。那么，这是否符合您的要求？

SELECT p.first_name AS first_name,
       p.last_name AS last_name,
       COUNT(*) AS goal_scored
FROM player p JOIN
     goal g
     ON g.player_marker_id = p.id JOIN
     `match` m
     ON g.match_id = m.id
WHERE m.round_id = :round_id AND
      :team_id IN (m.home_team_id, m.away_team_id) AND
      g.type IN (1, 2)
GROUP BY p.first_name, p.last_name
ORDER BY goal_scored DESC
LIMIT 5;

只有当你想要没有进球的球员时，你才会使用 LEFT JOIN。