无法通过连接获得正确的计数 [英] Trouble getting correct count with a join

问题描述

我正在尝试使用下表计算团队中每位球员的进球数和助攻数.一个球员可以在一个赛季中效力多支球队.我的结果计算了所有球队的总助攻数.如何将助攻限制在单个团队中?

I'm trying to calculate the goals and assists for every player on a team using the tables below. A player can play on multiple teams in a single season. My results count the total assists from all teams. How can I limit assists to a single team?

我的查询包含在下面.我期待的结果是格雷茨基的 2 个进球和 1 次助攻.我不希望结果中包含辅助 ID 3.

My query is included below. The results I'm expecting are 2 goals and 1 assist for Gretzky. I don't want assist id 3 included in the results.

mysql> select * from players;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Wayne      | Gretzky   |
|  2 | Mario      | Lemieux   |
|  3 | Mark       | Messier   |
+----+------------+-----------+

mysql> select * from teams;
+----+-----------+
| id | team_name |
+----+-----------+
|  1 | Oilers    |
|  2 | Penguins  |
|  3 | Kings     |
+----+-----------+

mysql> select * from goals;
+----+-----------+---------+---------+
| id | player_id | team_id | game_id |
+----+-----------+---------+---------+
|  1 |         1 |       1 |       1 |
|  2 |         1 |       1 |       1 |
|  3 |         3 |       1 |       1 |
|  4 |         2 |       2 |       1 |
|  5 |         3 |       3 |       2 |
+----+-----------+---------+---------+

mysql> select * from assists;
+----+---------+-----------+
| id | goal_id | player_id |
+----+---------+-----------+
|  1 |       1 |         3 |
|  2 |       3 |         1 |
|  3 |       5 |         1 |
+----+---------+-----------+

mysql> select players.id, players.last_name,
    -> count(distinct goals.id) as 'Goals',
    -> count(distinct assists.id) as 'Assists'
    -> from players
    -> join goals on players.id = goals.player_id
    -> left join assists on players.id = assists.player_id
    -> join teams on goals.team_id = teams.id
    -> where teams.id = 1
    -> group by players.id;
+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  1 | Gretzky   |     2 |       2 |
|  3 | Messier   |     1 |       1 |
+----+-----------+-------+---------+

推荐答案

我认为最直接的方法是使用相关的子查询.

I think the most straightforward approach to what you're attempting to do is to just use correlated sub queries.

因此，下面的第一个示例返回您要查找的结果.您可以轻松修改它以排除零进球和零助攻的行.

So, the first example just below returns the results you're looking for. You could easily modify it to exclude the rows with zero goals and assists.

它在每个子查询中使用 team_id 值，但您可以为它提供一个变量或参数，如图所示，这样您只需指定一次该值:

It uses the team_id value in each subquery, but you can supply that with a variable or parameter, as shown, so that you only need to specify the value once:

set @team_id := 2;

select
    p.id as player_id
    , p.last_name
    , (
        select count(*)
        from goals
        where player_id = p.id
        and team_id = @team_id
    ) as goals
    , (
        select count(*)
        from assists
        inner join goals on assists.goal_id = goals.id
        where assists.player_id = p.id
        and goals.team_id = @team_id
    ) as assists
from players p

对于团队 1:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  1 | Gretzky   |     2 |       1 |
|  2 | Lemieux   |     0 |       0 |
|  3 | Messier   |     1 |       1 |
+----+-----------+-------+---------+

对于团队 2:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  1 | Gretzky   |     0 |       0 |
|  2 | Lemieux   |     1 |       0 |
|  3 | Messier   |     0 |       0 |
+----+-----------+-------+---------+

对于第 3 组:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  1 | Gretzky   |     0 |       1 |
|  2 | Lemieux   |     0 |       0 |
|  3 | Messier   |     1 |       0 |
+----+-----------+-------+---------+

尾声

从尝试使用较少的子查询和/或聚合查询执行此操作的角度来看，您在第一次尝试时遇到了一些问题.

From the perspective of trying to do this with fewer subqueries and/or with aggregate queries, you have a couple of issues going on with your first attempt.

一个问题是，如果您没有在 group by 子句中包含所有字段，您的查询可能无法正常工作，即使 MySQL 不会像 (大多数?)其他数据库会.

One issue is that your query probably won't work correctly if you don't include all of the fields in your group by clause even though MySQL won't gripe at you about that like (most?) other databases will.

此外，由于您的助攻表和球员表中的记录仅通过进球表与球队间接相关，因此仅通过一个查询就很难独立汇总这两个进球和助攻.

Also, because records in both your assists and players tables are only indirectly related to teams through the goals table, it's pretty tough to get an independent rollup on both goals and assists with just one query.

举个例子，对此的其他早期答案，包括我的第一次快速尝试，有几个问题:

As an illustration of sorts, other early answers to this, including my first quick shot at it, had a couple of issues:

• 如果一名球员为球队助攻，但对该球队没有任何进球，则查询无法返回该球员和球队组合的任何结果.结果不完整.

• If a player had assists for a team, but didn't have any goals for that team, the queries couldn't return any results for that player and team combination. The results were incomplete.

如果一名球员为一支球队设定了目标，但没有为该球队提供助攻，那么查询仍然会返回一个正数来表示助攻，而他们应该返回零.结果实际上是错误的，而不仅仅是不完整.

If a player had goals for a team, but had no assists for that team, the queries would still return a positive number for assists when they should have returned zero. The results were actually wrong, not just incomplete.

下面是一个稍微更正确但仍然不完整的解决方案.它正确地指示玩家是否没有助攻，尽管返回 null 而不是 0，这是不幸的.

Just below is a slightly more correct, but still incomplete solution. It correctly indicates if a player has no assists, albeit by returning null instead of 0 which is unfortunate.

但这仍然是部分解决方案，因为如果一名球员没有为球队设定任何目标，您仍然不会看到该球员和球队组合的任何助攻.

But it's still a partial solution because if a player doesn't have any goals for a team, you still won't see any assists for that player and team combination.

这使用子查询作为聚合每个球员和球队助攻的虚拟表，如果有进球，但没有助攻，子查询的左外连接使它返回结果.

This uses a subquery as a virtual table that aggregates assists per player and team, and the left outer join to the subquery is what makes it return a result if there are goals, but no assists.

select
    p.id as player_id
    , p.last_name
    , count(g.game_id) as goals
    , a.assists
from players p
inner join goals g on p.id = g.player_id
left join (
    select
        assists.player_id
        , goals.team_id
        , count(assists.id) as assists
    from assists
    inner join goals on assists.goal_id = goals.id
    group by player_id, team_id, assists.id
) a
on g.player_id = a.player_id and g.team_id = a.team_id
where g.team_id = 1
group by player_id, last_name, g.team_id

该查询返回以下结果:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  1 | Gretzky   |     2 |       1 |
|  3 | Messier   |     1 |       1 |
+----+-----------+-------+---------+

为第 2 队运行此命令，您将得到以下结果，表明 Lemieux 没有为第 2 队提供任何助攻，但对于其他两名球员根本没有任何结果，他们没有助攻也没有球队进球2:

Run this for team 2, and you get these next results, indicating that Lemieux doesn't have any assists for team 2, but returning no results at all for the other two players, who have no assists and no goals for team 2:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  2 | Lemieux   |     1 |    null |
+----+-----------+-------+---------+

最后，为第 3 队运行它，您会得到下一个结果，表明 Messier 没有为第 3 队提供任何助攻.但是 Gretzky 丢失了，尽管他确实为第 3 队提供了助攻，因为他没有对第 3 队没有任何目标.所以解决方案不完整:

Finally, run it for team 3, and you get these next results, indicating that Messier doesn't have any assists for team 3. But Gretzky is missing, even though he does have an assist for team 3, because he doesn't have any goals for team 3. So the solution is not complete:

+----+-----------+-------+---------+
| id | last_name | Goals | Assists |
+----+-----------+-------+---------+
|  3 | Messier   |     1 |    null |
+----+-----------+-------+---------+

这篇关于无法通过连接获得正确的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！