mysql - SQL join multiple tables with count

Question

表格说明:

Table Members:
  id
  name
  status

Table Memberships:
  id
  date_start
  date_end
  date_added
  cost
  member_id
  status
  membership_type

Table Things:
  id
  name
  quantity
  member_id
  status

通过以下查询，我想获得表/ View ，该表/ View 将为每个“成员”显示“成员身份”，每个“成员”的最后一列中将包含“事物”的总和。

逻辑是这样的:“MEMBERS”是一条记录一个成员，每个“MEMBER”可以有多个“MEMBERSHIPS”，每个“MEMBER”可以有多个“THINGS”记录。

但是，以下查询返回事物数量乘以每个用户拥有的成员(member)数量的总和。

  select
  memberships.id,
  memberships.member_id,
  members.name,
  members.status,
  memberships.membership_type,
  memberships.date_start,
  memberships.date_end,
  memberships.date_added,
  memberships.cost,
  memberships.status
  sum(things.quantity) as quantity
  from memberships
  right join members on memberships.member_id = members.id
  right join things on things.member_id = members.id
  where NOW() between memberships.date_start and memberships.date_end
  and memberships.status = 1
  and things.status = 1
  and members.status = 1
  group by members.id, memberships.member_id

Answer 1

您需要在单独的子查询上计算每条记录，以便获得正确的结果。如果你连接表并直接计算它，你将得到无效的结果，因为 members 可以在 membership 表上有多个记录以及多个 things在 Things 表上。

SELECT  a.*,
        COALESCE(b.totalMembership, 0) totalMembership,
        COALESCE(c.TotalThings, 0) TotalThings
FROM    Members a
        LEFT JOIN
        (
            SELECT  member_id, COUNT(*) totalMembership
            FROM    Memberships 
            GROUP   BY member_ID
        ) b ON a.ID = b.member_ID
        LEFT JOIN 
        (
            SELECT  member_ID, SUM(quantity) TotalThings
            FROM    Things
            GROUP   BY member_ID
        ) c ON a.ID = c.member_ID