子查询返回了 1 个以上的值.VB6.

Question

嗨，我正在尝试选择movementline.qty的值，但前提是movements.movementscode等于RW，如果不只是放0.00

SELECT levelfield1.description,
       products.reportuom,
       products.productcode,
       products.description,
       Isnull(Sum(ReceivingLine.qty), 0.00)       AS [B.Delivery],
       movements.movementcode,
       Isnull((SELECT qty
               FROM   MovementLine
               WHERE  movementcode = 'RW'), 0.00) AS [B. Returned]
FROM   Products
       LEFT JOIN LevelField1
              ON levelfield1.levelfield1code = products.levelfield1code
       LEFT JOIN ReceivingLine
              ON receivingline.PRODUCTCODE = products.productcode
       LEFT JOIN MovementLine
              ON movementline.ProductCode = products.productcode
       LEFT JOIN Movements
              ON movements.MovementID = MovementLine.movementid
GROUP  BY levelfield1.Description,
          products.reportuom,
          products.productcode,
          products.description,
          movementline.qty,
          movements.movementcode 

我收到错误

子查询返回了 1 个以上的值。在以下情况下不允许这样做 子查询跟随 =、！=、<、<= 、>、>= 或当子查询用作 一个表达式。警告：空值被聚合或 其他设置操作。

Answer 1

你的代码需要深入思考。因为可能存在连接问题，您可能无法获得正确的结果。问题将使用前 1 名解决，但不可能获得正确的结果。所以这里需要使用CASE。我希望您的问题将通过使用以下代码来解决。

select levelfield1.description, products.reportuom, products.productcode,
        products.description ,ISNULL(SUM(ReceivingLine.qty),0.00) as [B.Delivery], 
  movements.movementcode,
  CASE 
    WHEN movementline.movementcode = 'RW' THEN ISNULL(movementline.qty,0.00)
    ESLE 0.00
  END AS [Returned]
  from Products 
  left join LevelField1 on levelfield1.levelfield1code = products.levelfield1code
  left join ReceivingLine on receivingline.PRODUCTCODE = products.productcode
  left join MovementLine on movementline.ProductCode = products.productcode
  left join Movements on movements.MovementID = MovementLine.movementid 
  group by levelfield1.Description, products.reportuom, products.productcode, products.description, movementline.qty,movements.movementcode