如何按关联计数分组结果？基于会员数查询Profiles表数据

嘿，我来帮你搞定这个需求！你已经写出了统计单个Profile会员数的基础查询，接下来只要在此之上再加一层分组统计，就能得到你想要的结果了，咱们一步一步说：

1. 先确认基础的会员数统计（优化版） #

你原来的SQL已经能拿到每个Profile对应的会员数，不过稍微调整一下会更清晰——用profiles.id分组，统计memberships.id的数量（毕竟咱们要算的是会员记录数）：

SELECT 
  profiles.id AS profile_id, 
  COUNT(memberships.id) AS membership_count
FROM profiles
INNER JOIN memberships ON profiles.id = memberships.profile_id
GROUP BY profiles.id

这个查询会返回每一条Profile的ID，以及它对应的会员数量。

2. 按会员数分组统计Profile数量 #

如果想知道「有多少个Profile拥有2个会员」，或者「各会员数对应的Profile分布」，咱们可以把上面的查询作为子查询，在外层再做一次分组：

方式一：查看所有会员数对应的Profile数量 #

SELECT 
  membership_count, 
  COUNT(profile_id) AS profile_count
FROM (
  -- 内层就是刚才的基础统计查询
  SELECT 
    profiles.id AS profile_id, 
    COUNT(memberships.id) AS membership_count
  FROM profiles
  INNER JOIN memberships ON profiles.id = memberships.profile_id
  GROUP BY profiles.id
) AS profile_membership_counts
GROUP BY membership_count

这个结果会列出每个会员数（比如1、2、3...），以及对应有多少个Profile达到这个会员数。

方式二：只查询特定会员数的Profile数量 #

如果你只想找拥有2个会员的Profile数量，直接在外层加WHERE筛选即可：

SELECT COUNT(profile_id) AS profile_count
FROM (
  SELECT 
    profiles.id AS profile_id, 
    COUNT(memberships.id) AS membership_count
  FROM profiles
  INNER JOIN memberships ON profiles.id = memberships.profile_id
  GROUP BY profiles.id
) AS profile_membership_counts
WHERE membership_count = 2

3. 可选：包含无会员的Profile #

如果你想把没有任何会员的Profile（会员数为0）也统计进去，只要把INNER JOIN改成LEFT JOIN就行——LEFT JOIN会保留所有Profile记录，没有会员的Profile对应的memberships.id会是NULL，COUNT(NULL)会返回0：

SELECT 
  membership_count, 
  COUNT(profile_id) AS profile_count
FROM (
  SELECT 
    profiles.id AS profile_id, 
    COUNT(memberships.id) AS membership_count
  FROM profiles
  LEFT JOIN memberships ON profiles.id = memberships.profile_id
  GROUP BY profiles.id
) AS profile_membership_counts
GROUP BY membership_count

这样就能覆盖所有场景啦！

内容的提问来源于stack exchange，提问作者Hat