MySQL查询-将3个表连接在一起，按一列分组，另2列计数 [英] MySQL query - join 3 tables together, group by one column and count for the other 2

问题描述

以下是我正在使用的3个表的示例.

Here are examples of the 3 tables I'm working with.

    Teams
    +----+------+
    | id | name |
    +----+------+
    |  1 | abc  |
    |  2 | def  |
    |  3 | ghi  |
    +----+------+

    Members
    +----+-----------+----------+---------+
    | id | firstname | lastname | team_id |
    +----+-----------+----------+---------+
    |  1 | joe       | smith    |       1 |
    |  2 | jared     | robinson |       1 |
    |  3 | sarah     | cole     |       3 |
    |  4 | jaci      | meyers   |       2 |
    +----+-----------+----------+---------+

    Goals
    +----+-----------+
    | id | member_id |
    +----+-----------+
    |  1 |         3 |
    |  2 |         2 |
    |  3 |         2 |
    |  4 |         3 |
    |  5 |         1 |
    +----+-----------+
    

我正在尝试查询输出类似这样的内容...

And I'm trying to get a query that outputs something like this ...

    Output
    +--------+----------------+-------------+
    | t.name | Count(members) | Count(goals)|
    +--------+----------------+-------------+
    | abc    |              2 |           3 |
    | def    |              1 |           2 |
    | ghi    |              1 |           0 |
    +--------+----------------+-------------+
    

这是我最近的一次，但是当我在子查询中使用 group by 时，出现子查询返回多于1行"的信息.

This is the closest I've come, but when I use the group by in the subquery I get "Subquery returns more than 1 row".

select t.name, count(*), 
    (select count(*)
    from teams t 
    inner join members m on m.team_id = t.id
    group by t.id)
from teams t 
inner join members m on m.team_id = t.id 
inner join goals g on g.member_id = m.id
group by t.id

推荐答案

根据我的理解，这是我提出的查询:

Based on my understanding, here is the query that I come up with:

    SELECT name, membersCount, IFNULL(totalCount, 0) goalsCount FROM
    (
      SELECT m.team_id, SUM(innerQuery.goalsCount) totalCount
      FROM (
        SELECT m.id memberId, COUNT(*) goalsCount
        FROM Members m
        JOIN Goals g
        ON m.id = g.member_id
        GROUP BY member_id
      ) innerQuery
      JOIN Members m
      ON innerQuery.memberId = m.id
      GROUP BY m.team_id
    ) inner_1
    RIGHT JOIN 
    (
      SELECT t.id, t.name, COUNT(*) membersCount
      FROM Teams t
      JOIN Members m
      ON t.id = m.team_id
      GROUP BY team_id
    ) inner_2
    ON inner_1.team_id = inner_2.id

查询的细分:

#1.获取会员ID及其相关目标数(innerQuery)

#1. Get the member ID with its associated goals count (innerQuery)

SELECT m.id memberId, COUNT(*) goalsCount
    FROM Members m
    JOIN Goals g
    ON m.id = g.member_id
    GROUP BY member_id

#2.获取团队ID和目标的总和(inner_1)

#2. Get the team id for with the total SUM of the goals (inner_1)

     SELECT m.team_id, SUM(innerQuery.goalsCount) totalCount
      FROM (
          .... Sub-query in step 1
      ) innerQuery
      JOIN Members m
      ON innerQuery.memberId = m.id
      GROUP BY m.team_id

#3.获取每个团队的成员总数(inner_2)

#3. Get total members count per team (inner_2)

    SELECT t.id, t.name, COUNT(*) membersCount
      FROM Teams t
      JOIN Members m
      ON t.id = m.team_id
      GROUP BY team_id

#4.右联接inner_1和inner_2(因为将有NULL)，并使用IFNULL来检查并替换为0

#4. RIGHT JOIN inner_1 and inner_2 (since there will be NULL) and use IFNULL to check and replace that 0

    SELECT name, membersCount, IFNULL(totalCount, 0) goalsCount FROM
    (
     .... Sub-query in step 2
    ) inner_1
    RIGHT JOIN 
    (
      .... Sub-query in step 3
    ) inner_2
    ON inner_1.team_id = inner_2.id

这篇关于MySQL查询-将3个表连接在一起，按一列分组，另2列计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！