SQL子查询返回多行值错误排查及可行解决方案咨询

错误原因 #

你遇到的报错核心原因是：CASE语句的THEN/ELSE分支里的子查询返回了多行结果。SQL明确规定，当子查询作为表达式（比如在CASE分支里赋值）或者跟在=、<等比较运算符后时，必须返回且仅返回单个值。移除其中一个分支后能正常运行，说明剩下的分支对应的子查询刚好都返回单行，但两个分支都保留时，存在某些行对应的子查询返回多行，触发了错误。

解决办法 #

方案1：强制子查询返回单个值（聚合函数） #

如果业务逻辑允许对多行结果做汇总（比如求和），可以在子查询中使用SUM聚合函数，确保始终返回单个值；如果只需要取某一行数据，也可以用TOP 1（记得搭配ORDER BY保证结果稳定）。

修改后的SQL示例（以SUM为例）：

SELECT
CASE
  WHEN ISNULL((
    SELECT xapIMCostingMethod
    FROM ProductionProperties), 0) = 2
  THEN ISNULL((
    SELECT SUM(CAST(ROUND((intUnitMaterialCost) * (-imtInventoryQuantityReceived + imtScrapQuantity), 2) AS NUMERIC(36, 2)))
    FROM PartTransactionCosts
    WHERE intPartTransactionID = imtPartTransactionID
    AND intCostType = 2), 0)
  ELSE ISNULL((
    SELECT SUM(CAST(ROUND((intUnitMaterialCost) * (-imtInventoryQuantityReceived + imtScrapQuantity), 2) AS NUMERIC(36, 2)))
    FROM PartTransactionCosts
    WHERE intPartTransactionID = imtPartTransactionID
    AND intCostType = 1), 0)
END AS ActMatCost
FROM PartTransactions

方案2：改用JOIN关联查询（性能更优） #

把子查询转换成LEFT JOIN的形式，避免在CASE里嵌套子查询，同时能更清晰地处理多行数据的场景：

SELECT
  CASE
    WHEN ISNULL(pp.xapIMCostingMethod, 0) = 2
    THEN ISNULL(ptc2.cost_value, 0)
    ELSE ISNULL(ptc1.cost_value, 0)
  END AS ActMatCost
FROM PartTransactions pt
CROSS JOIN (SELECT xapIMCostingMethod FROM ProductionProperties) pp
LEFT JOIN (
  SELECT 
    intPartTransactionID,
    SUM(CAST(ROUND((intUnitMaterialCost) * (-imtInventoryQuantityReceived + imtScrapQuantity), 2) AS NUMERIC(36, 2))) AS cost_value
  FROM PartTransactionCosts
  WHERE intCostType = 2
  GROUP BY intPartTransactionID
) ptc2 ON pt.intPartTransactionID = ptc2.intPartTransactionID
LEFT JOIN (
  SELECT 
    intPartTransactionID,
    SUM(CAST(ROUND((intUnitMaterialCost) * (-imtInventoryQuantityReceived + imtScrapQuantity), 2) AS NUMERIC(36, 2))) AS cost_value
  FROM PartTransactionCosts
  WHERE intCostType = 1
  GROUP BY intPartTransactionID
) ptc1 ON pt.intPartTransactionID = ptc1.intPartTransactionID

这个方案先将PartTransactionCosts按intPartTransactionID和intCostType分组汇总，再关联到主表，性能比嵌套子查询更好，也从根源上避免了子查询返回多行的问题。

额外排查项 #

如果业务逻辑中每个intPartTransactionID+intCostType组合应该只有一行数据，那你需要检查PartTransactionCosts表是否存在重复数据，清理重复行也能直接解决该报错。

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