首页 文章

子查询返回的值超过1 . VB6

提问于
浏览 次
0

嗨，我正在尝试选择 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 . 当子查询遵循=，！=，<，<=，>，> =或子查询用作表达式时，不允许这样做 . 警告：聚合或其他SET操作消除了空值 .

sql sql-server

1 回答

  • 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
    回复于

相关问题