ORA-01427报错：子查询返回多行时如何统计各agent的活跃连接数

错误原因 #

你触发ORA-01427的核心原因是：like运算符要求右侧为单值，但你写的子查询返回了所有agent名称的多行结果，SQL引擎无法完成多值匹配。同时原有写法的逻辑也不可能输出连接数为0的agent——当agent没有有效连接时，connection表中没有对应记录，自然不会出现在分组结果里。

解决方法 #

使用左外连接实现需求：以存储全量agent的表作为主表，关联满足有效条件的连接表，关联不到的agent会自动保留，计数时统计连接表的非空字段即可得到0值。
正确SQL如下：

SELECT 
  COUNT(conn.agent) AS connect_count,
  ag.agent_name AS agent
FROM 
  (SELECT UNIQUE(UPPER(agent_name)) AS agent_name FROM admin.agent) ag
LEFT JOIN 
  admin.connection conn 
  ON UPPER(conn.agent) = ag.agent_name
  AND conn.is_active = 1
  AND conn.is_currver = 1
GROUP BY 
  ag.agent_name
ORDER BY 
  ag.agent_name;

补充说明 #

• 左连接的过滤条件要写在ON子句里，不能写在WHERE中：如果写在WHERE中，会把连接不到的agent记录过滤掉，还是得不到连接数为0的结果
• 如果确实需要模糊匹配而不是等值匹配，把ON后的=替换为LIKE即可，示例：ON UPPER(conn.agent) LIKE '%' || ag.agent_name || '%'
• COUNT(conn.agent)只会统计conn.agent非空的记录，没有有效连接的agent对应的conn.agent为null，计数结果自然为0

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