GROUP_CONCAT嵌套子查询报1242错误：子查询返回多行的技术求助

你遇到的21000/1242: Subquery returns more than 1 row错误，核心原因是：你直接将一个返回多行结果的子查询塞进了GROUP_CONCAT()中，而这种写法要求内部子查询必须是标量子查询（仅能返回单个值），但你的子查询SELECT StockID FROM stock_file WHERE SKU = C.SKU会输出多个StockID，因此触发了报错。

下面提供两种可行的修正方案：

方案一：改用JOIN关联查询（推荐，性能更优） #

重新调整内层查询结构，通过JOIN直接关联stock_file获取对应StockID，再用GROUP_CONCAT聚合结果：

SELECT T.SKU, SUM(T.InStk) as 'InStk', T.MainId, GROUP_CONCAT(T.ParentIds) as 'ParentIds', T.Product_Name1 as 'Description' 
FROM 
  (
    SELECT A.SKU, A.Depot, MAX(A.Qty) as 'InStk', B.StockID as 'MainId', GROUP_CONCAT(D.StockID) as 'ParentIds', B.Product_Name1 
    FROM stock_depot_qty as A 
    LEFT JOIN stock_file as B ON A.SKU = B.SKU 
    LEFT JOIN stock_custom_options as C ON A.SKU = (SELECT SKU FROM stock_file WHERE StockID = C.StockID LIMIT 1)
    LEFT JOIN stock_file as D ON D.SKU = C.SKU -- 直接关联获取对应StockID
    WHERE A.SKU != '' 
    GROUP BY A.SKU, A.Depot, B.StockID, B.Product_Name1 -- 需包含所有非聚合字段，避免ONLY_FULL_GROUP_BY模式报错
  ) as T 
GROUP BY T.SKU, T.MainId, T.Product_Name1;

方案二：子查询内部先聚合（兼容原有结构） #

如果想保留嵌套子查询的写法，可先在子查询内部用GROUP_CONCAT将多行StockID合并为单个字符串，再传入外层的GROUP_CONCAT：

SELECT T.SKU, SUM(T.InStk) as 'InStk', T.MainId, GROUP_CONCAT(T.ParentIds) as 'ParentIds', T.Product_Name1 as 'Description' 
FROM 
  (
    SELECT A.SKU, A.Depot, MAX(A.Qty) as 'InStk', B.StockID as 'MainId', 
           (SELECT GROUP_CONCAT(StockID) FROM stock_file WHERE SKU = C.SKU) as 'ParentIds', 
           B.Product_Name1 
    FROM stock_depot_qty as A 
    LEFT JOIN stock_file as B ON A.SKU = B.SKU 
    LEFT JOIN stock_custom_options as C ON A.SKU = (SELECT SKU FROM stock_file WHERE StockID = C.StockID LIMIT 1) 
    WHERE A.SKU != '' 
    GROUP BY A.SKU, A.Depot, B.StockID, B.Product_Name1
  ) as T 
GROUP BY T.SKU, T.MainId, T.Product_Name1;

额外注意 #

• 确保GROUP BY子句包含所有非聚合的SELECT字段，否则在MySQL开启ONLY_FULL_GROUP_BY严格模式时会触发语法错误。
• 优先选择方案一，嵌套子查询的性能通常比关联查询差，数据量较大时差异会更明显。

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