为多表关联SQL查询添加用户有效订阅组计数列时遇GROUP BY报错咨询

• 你在查询中引入了聚合函数COUNT()，但没有配套声明GROUP BY子句。关系型数据库的硬性规则是：所有出现在SELECT列表中、没有被聚合函数包裹的字段，必须全部纳入GROUP BY的分组规则。否则数据库没有办法判断，每个分组里要取哪一行的值来展示这些非聚合字段，就会直接抛出column must appear in the GROUP BY clause的语法错误。
• 你之前的JOIN改造还有个细节疏漏：给新关联的订阅组表设置了all_sg别名后，关联条件仍然写的是account_user.id = account_subscriptiongroup.user_id，实际引用的是原查询已经关联的单条订阅组表（对应单条管理员操作关联的那个订阅组），而非你打算统计的全量订阅组表，会进一步造成数据关联错位。
• 就算你补全了GROUP BY子句把报错消掉，这种改造方式也不符合最初的查询目标：原查询是要逐行展示每一条用户终止订阅的管理员操作明细，一旦通过JOIN关联上用户的所有订阅组再做聚合，会把原本的操作明细行压缩成用户维度的汇总行，丢失单条操作的展示信息。

不需要改动原查询的过滤、关联逻辑，通过预计算关联的方式新增计数列，既不影响原查询的明细粒度，也不会因为关联多表造成行膨胀，性能表现更好：

• 先在主查询的关联部分，新增一个预计算的派生表，提前统计每个用户的有效订阅组数量
• 把派生表通过用户ID和主查询关联，直接取出对应计数即可

完整可运行的改造后SQL #

SELECT 
      account_user.id as user_id, 
      account_user.email, 
      account_user.first_name, 
      account_user.last_name, 
      account_user.phone,
      account_subscriptiongroup.id as sub_group_id,
      account_adminaction.description,
      account_adminaction.id as admin_action_id,
      account_adminaction.created_on  as subscription_ended_on,
      sg_count.active_sub_group_count -- 新增计算列：对应用户的当前有效订阅组数量
FROM 
      account_adminaction
LEFT JOIN
      account_user ON account_user.id = account_adminaction.user_id
LEFT JOIN 
      account_subscriptiongroup ON account_adminaction.sub_group_id = account_subscriptiongroup.id
-- 预计算每个用户的有效订阅组数，避免主查询行膨胀
LEFT JOIN (
    SELECT 
      user_id,
      COUNT(*) as active_sub_group_count
    FROM account_subscriptiongroups
    WHERE active = true
    GROUP BY user_id
) sg_count ON sg_count.user_id = account_user.id
WHERE 
      account_adminaction.created_on >= '2021-04-07' AND 
      account_adminaction.created_on <= '2021-04-13' AND
      (
        (account_adminaction.description LIKE 'Arrêt de l''abonnement%') 
        OR (account_adminaction.description LIKE 'L''utilisateur a arrêté%')
      )
ORDER BY
      subscription_ended_on

其他可选实现方式 #

如果主查询数据量不大，也可以直接在SELECT列表中嵌入相关标量子查询，不需要新增JOIN关联，写法更简单：

-- 直接把这段加到原SELECT列表的末尾即可
(
    SELECT COUNT(*) 
    FROM account_subscriptiongroups sg
    WHERE sg.active = true 
      AND sg.user_id = account_user.id
) as active_sub_group_count

注意：这种逐行执行的子查询在数据量大时性能会低于预计算派生表的方式，根据实际数据规模选择即可。

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