SQL查询报‘single-row subquery returns more than one row’错误求排查

这个错误的核心原因很明确：CASE表达式的每个分支必须返回单个标量值，但你第一个WHEN分支里的子查询没有做任何单行限制，当它匹配到多条记录时就会触发这个报错。

问题定位 #

看你的代码片段：

CASE WHEN s.servprov_gid = 'IFFCO.CAR-60041' THEN 
  (SELECT E.EQUIPMENT_NUMBER FROM S_EQUIPMENT SE, EQUIPMENT E, SHIPMENT_S_EQUIPMENT_JOIN SSEJ, SHIPMENT S WHERE ...)
ELSE 
  (SELECT MIN(se.equipment_number) FROM ...)
END

• ELSE分支用了MIN()聚合函数，无论匹配多少条记录，都会返回唯一的最小值，所以不会出错。
• 但第一个WHEN分支的子查询是直接查询E.EQUIPMENT_NUMBER，如果SHIPMENT_GID='IFFCO/LOGISTICS.L171203007'对应的设备记录不止一条，这个子查询就会返回多行，而CASE无法处理多行结果，因此抛出错误。

修复方案 #

你有两种常见的修复方式，根据业务需求选择：

1. 给第一个子查询添加聚合限制（比如取最小值/最大值）

如果你只需要任意一个设备编号（比如最小的），可以给第一个子查询也加上MIN()，和ELSE分支保持逻辑一致：

CASE 
  WHEN s.servprov_gid = 'IFFCO.CAR-60041' THEN 
    (SELECT MIN(E.EQUIPMENT_NUMBER) 
     FROM S_EQUIPMENT SE, EQUIPMENT E, SHIPMENT_S_EQUIPMENT_JOIN SSEJ, SHIPMENT S 
     WHERE SSEJ.SHIPMENT_GID = 'IFFCO/LOGISTICS.L171203007' 
       AND SE.S_EQUIPMENT_GID = SSEJ.S_EQUIPMENT_GID 
       AND E.EQUIPMENT_GID = SE.EQUIPMENT_GID 
       AND SSEJ.SHIPMENT_GID = S.SHIPMENT_GID) 
  ELSE 
    (SELECT MIN(se.equipment_number) 
     FROM shipment_s_equipment_join ssej, s_equipment se 
     WHERE ssej.shipment_gid = 'IFFCO/LOGISTICS.L171203007' 
       AND ssej.s_equipment_gid = se.s_equipment_gid 
       AND se.equipment_number IS NOT NULL) 
END 

2. 只取匹配到的第一条记录

如果你需要特定的某一条（比如按编号排序后的第一条），可以用数据库对应的单行限制语法：

• Oracle环境用ROWNUM = 1（需嵌套子查询实现排序）：

  CASE 
    WHEN s.servprov_gid = 'IFFCO.CAR-60041' THEN 
      (SELECT eq.EQUIPMENT_NUMBER
       FROM (
         SELECT E.EQUIPMENT_NUMBER
         FROM S_EQUIPMENT SE, EQUIPMENT E, SHIPMENT_S_EQUIPMENT_JOIN SSEJ, SHIPMENT S 
         WHERE SSEJ.SHIPMENT_GID = 'IFFCO/LOGISTICS.L171203007' 
           AND SE.S_EQUIPMENT_GID = SSEJ.S_EQUIPMENT_GID 
           AND E.EQUIPMENT_GID = SE.EQUIPMENT_GID 
           AND SSEJ.SHIPMENT_GID = S.SHIPMENT_GID
         ORDER BY E.EQUIPMENT_NUMBER -- 按业务需求排序
       ) eq
       WHERE ROWNUM = 1) 
    ELSE 
      (SELECT MIN(se.equipment_number) 
       FROM shipment_s_equipment_join ssej, s_equipment se 
       WHERE ssej.shipment_gid = 'IFFCO/LOGISTICS.L171203007' 
         AND ssej.s_equipment_gid = se.s_equipment_gid 
         AND se.equipment_number IS NOT NULL) 
  END 
  

• MySQL/PostgreSQL环境用LIMIT 1：

  CASE 
    WHEN s.servprov_gid = 'IFFCO.CAR-60041' THEN 
      (SELECT E.EQUIPMENT_NUMBER
       FROM S_EQUIPMENT SE, EQUIPMENT E, SHIPMENT_S_EQUIPMENT_JOIN SSEJ, SHIPMENT S 
       WHERE SSEJ.SHIPMENT_GID = 'IFFCO/LOGISTICS.L171203007' 
         AND SE.S_EQUIPMENT_GID = SSEJ.S_EQUIPMENT_GID 
         AND E.EQUIPMENT_GID = SE.EQUIPMENT_GID 
         AND SSEJ.SHIPMENT_GID = S.SHIPMENT_GID
       ORDER BY E.EQUIPMENT_NUMBER
       LIMIT 1) 
    ELSE 
      (SELECT MIN(se.equipment_number) 
       FROM shipment_s_equipment_join ssej, s_equipment se 
       WHERE ssej.shipment_gid = 'IFFCO/LOGISTICS.L171203007' 
         AND ssej.s_equipment_gid = se.s_equipment_gid 
         AND se.equipment_number IS NOT NULL) 
  END 
  

额外建议 #

你也可以尝试用JOIN的方式重构这段逻辑，代替嵌套子查询，这样代码可读性更高，也更容易排查类似的行数问题。

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