使用GROUP BY时如何从聚合子查询返回字段到外层SELECT

原写法的标量子查询没有和外层查询做agent_id维度的关联，子查询内GROUP BY agent_id会返回全表所有agent在city='x'下的统计结果，外层每一行查询期望子查询返回单个聚合值，匹配到多行结果就会触发「Single-row subquery returns more than one row」错误。

要求不使用CTE、仅用子查询实现的场景下，使用关联标量子查询即可，核心是给每个子查询加上和外层agent_id的关联条件，且子查询内不需要额外写GROUP BY agent_id——关联条件已经把统计范围限定为当前外层行对应的单个agent，聚合后自然只会返回单个值。

以下是完整可运行的SQL，覆盖全量交易笔数、销售总额、佣金总额，以及city=x/y/z三个城市对应的三项指标：

SELECT 
  t1.agent_id,
  -- 全量维度聚合指标
  COUNT(t1.transaction_id) AS trans_count_all,
  SUM(t1.sales_amount) AS sales_total_all,
  SUM(t1.commission) AS commission_total_all,
  -- city=x 维度聚合指标
  (SELECT COUNT(transaction_id) FROM table1 WHERE agent_id = t1.agent_id AND city = 'x') AS trans_count_x,
  (SELECT SUM(sales_amount) FROM table1 WHERE agent_id = t1.agent_id AND city = 'x') AS sales_total_x,
  (SELECT SUM(commission) FROM table1 WHERE agent_id = t1.agent_id AND city = 'x') AS commission_total_x,
  -- city=y 维度聚合指标
  (SELECT COUNT(transaction_id) FROM table1 WHERE agent_id = t1.agent_id AND city = 'y') AS trans_count_y,
  (SELECT SUM(sales_amount) FROM table1 WHERE agent_id = t1.agent_id AND city = 'y') AS sales_total_y,
  (SELECT SUM(commission) FROM table1 WHERE agent_id = t1.agent_id AND city = 'y') AS commission_total_y,
  -- city=z 维度聚合指标
  (SELECT COUNT(transaction_id) FROM table1 WHERE agent_id = t1.agent_id AND city = 'z') AS trans_count_z,
  (SELECT SUM(sales_amount) FROM table1 WHERE agent_id = t1.agent_id AND city = 'z') AS sales_total_z,
  (SELECT SUM(commission) FROM table1 WHERE agent_id = t1.agent_id AND city = 'z') AS commission_total_z
FROM table1 t1
GROUP BY t1.agent_id;

• 代码中sales_amount为销售额字段、commission为佣金字段，使用时替换为表中实际对应的字段名即可。
• 如果某个agent在对应城市没有交易记录，子查询会返回NULL，需要展示为0的话，给每个子查询结果套一层COALESCE(子查询, 0)即可，示例：COALESCE((SELECT COUNT(transaction_id) FROM table1 WHERE agent_id = t1.agent_id AND city = 'x'), 0) AS trans_count_x。
• 如果数据量较大，可以给agent_id、city字段建联合索引，能大幅提升关联子查询的执行效率。

内容的提问来源于stack exchange，提问作者J-snow