MS SQL2008嵌套选择语句

Question

嗨，我正在尝试创建一个嵌套的SQL状态，但无法让它工作。我想要的声明是从采购订单表中输入最快的到期日。当数量>0时。不过，我不能让这件事起作用。我想以EffectiveDate的形式返回这个字段

SELECT  'Past Due Purchase Orders' AS TransactionType ,
        PurchaseOrders.PurchaseOrderId AS TransactionId ,
        ( SELECT TOP ( 1 )
                    DueDate
          FROM      PurchaseOrderItems
          WHERE     ( QuantityOutstanding > 0 )
          ORDER BY  DueDate
        ) AS EffectiveDate ,
        Suppliers.SupplierId ,
        Suppliers.Branch ,
        Suppliers.SupplierName ,
        Currencies.CurrencyDisplay ,
        CASE WHEN PurchaseOrderItems.ItemType = 'P'
             THEN Products.ProductId
             WHEN PurchaseOrderItems.ItemType = 'S' THEN Sundries.SundryId
             WHEN PurchaseOrderItems.ItemType = 'F'
             THEN FreightMethods.FreightMethodId
             ELSE PurchaseOrderItems.FreeTextItem
        END AS Item ,
        PurchaseOrderItems.ItemDescription ,
        PurchaseOrderItems.Quantity ,
        UnitsOfMeasureFactors.UnitOfMeasureFactorPrintText ,
        PurchaseOrderItems.CurItemValue AS TotalValue ,
        'PurchaseOrders' AS ScreenName ,
        CustomValues.CustomFieldValueBit AS AcknowledgementRx ,
        PurchaseOrders.DeliveryDateConfirmed ,
        CASE WHEN PurchaseOrders.DeliveryDateConfirmed = 0
                  AND CustomValues.CustomFieldValueBit = 0
             THEN 'Not Ack ¦ Not Conf'
             WHEN PurchaseOrders.DeliveryDateConfirmed = 1
                  AND CustomValues.CustomFieldValueBit = 0
             THEN 'Not Ack ¦ Conf'
             WHEN PurchaseOrders.DeliveryDateConfirmed = 0
                  AND CustomValues.CustomFieldValueBit = 1
             THEN 'Ack ¦ Not Conf'
             WHEN PurchaseOrders.DeliveryDateConfirmed = 1
                  AND CustomValues.CustomFieldValueBit = 1
             THEN 'Ack ¦ Conf'
        END AS Acktxt
FROM    PurchaseOrders
        LEFT OUTER JOIN PurchaseOrderItems ON PurchaseOrders.PurchaseOrder = PurchaseOrderItems.PurchaseOrder
        INNER JOIN Suppliers ON PurchaseOrders.Supplier = Suppliers.Supplier
        INNER JOIN Currencies ON PurchaseOrders.Currency = Currencies.Currency
        INNER JOIN CustomValues ON PurchaseOrders.PurchaseOrder = CustomValues.RecordNumber
        INNER JOIN CustomFields ON CustomValues.CustomField = CustomFields.CustomField
                                   AND CustomFields.CustomFieldId = 'Acknowledgement'
        LEFT OUTER JOIN Products ON PurchaseOrderItems.Product = Products.Product
        LEFT OUTER JOIN Sundries ON PurchaseOrderItems.Sundry = Sundries.Sundry
        LEFT OUTER JOIN FreightMethods ON PurchaseOrderItems.FreightMethod = FreightMethods.FreightMethod
        LEFT OUTER JOIN UnitsOfMeasureFactors ON UnitsOfMeasureFactors.UnitOfMeasureFactor = PurchaseOrderItems.UnitOfMeasureFactor
WHERE   ( PurchaseOrders.SystemType = 'F' )
        AND ( PurchaseOrderItems.DueDate < GETDATE() )
        AND ( PurchaseOrderItems.QuantityOutstanding > 0 )
ORDER BY TransactionType ,
        Currencies.CurrencyDisplay ,
        EffectiveDate ,
        TransactionId

Answer 1

您需要从外部语句引用表的子查询，如下所示：

( SELECT TOP 1
    DueDate
  FROM PurchaseOrderItems
  WHERE PurchaseOrderItems.PurchaseOrder = PurchaseOrders.PurchaseOrder
    AND QuantityOutstanding > 0
  ORDER BY DueDate
) AS EffectiveDate