sql - 基于子查询数量的结果集

Question

我有两个要组合的查询；基本上第一个查询找出特定产品的发货数量

SELECT 
    T0.SOPOrderReturnLineID,
    T1.Quantity AS 'QtyDespatched',
    T0.LineQuantity As 'OrderQty',
    T1.SopLinkId AS 'DespatchSopLinkId',
    T1.ItemCode 
FROM
    SOPOrderReturnLine AS T0
LEFT JOIN 
    DespatchedItems AS T1 ON T0.SOPOrderReturnLineID = T1.SopLinkId
WHERE
    T1.SopLinkId IS NOT NULL AND
    T1.ItemCode = 'GSNOBAS60GW'
GROUP BY 
    T1.SopLinkId, T0.SOPOrderReturnLineID, 
    T1.Quantity, T0.LineQuantity, T1.ItemCode

这是两个查询的示例结果集

第二个查询通过对我不希望出现在上述结果集中的项目的数量求和，找出总共制造了多少个项目，直到OrderQty匹配到目前为止已经完成的 3 是如何完成的？

SELECT 
    SUM(CONVERT(INT, T1.Quantity))
FROM 
    SOPOrderReturnLine AS T0
LEFT JOIN 
    DespatchedItems AS T1 ON T0.SOPOrderReturnLineID = T1.SopLinkId
WHERE
    T1.SopLinkId IS NOT NULL AND
    T1.ItemCode = 'GSNOBAS60GW'

标签： sqlsql-server

Answer 1

很确定这只是一个典型的聚合查询。像这样的东西。请注意，我将您的别名更改为与表格相关的内容。使用通用别名确实具有挑战性。https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-abc-or-t1-t2-t3

SELECT 
    ORL.SOPOrderReturnLineID,
    SUM(DI.Quantity) AS 'QtyDespatched',
    ORL.LineQuantity As 'OrderQty',
    DI.SopLinkId AS 'DespatchSopLinkId',
    DI.ItemCode 
FROM
    SOPOrderReturnLine AS ORL
LEFT JOIN 
    Fuel_DespatchedItems AS DI ON ORL.SOPOrderReturnLineID = DI.SopLinkId
WHERE
    DI.SopLinkId IS NOT NULL AND
    DI.ItemCode = 'GSNOBAS60GW'
GROUP BY 
    DI.SopLinkId
    , ORL.SOPOrderReturnLineID
    , ORL.LineQuantity
    , DI.ItemCode

如果要过滤聚合，请使用HAVING子句。

只需在分组后添加 HAVING 即可。

SELECT 
    ORL.SOPOrderReturnLineID,
    SUM(DI.Quantity) AS 'QtyDespatched',
    ORL.LineQuantity As 'OrderQty',
    DI.SopLinkId AS 'DespatchSopLinkId',
    DI.ItemCode 
FROM
    SOPOrderReturnLine AS ORL
LEFT JOIN 
    Fuel_DespatchedItems AS DI ON ORL.SOPOrderReturnLineID = DI.SopLinkId
WHERE
    DI.SopLinkId IS NOT NULL AND
    DI.ItemCode = 'GSNOBAS60GW'
GROUP BY 
    DI.SopLinkId
    , ORL.SOPOrderReturnLineID
    , ORL.LineQuantity
    , DI.ItemCode
HAVING SUM(DI.Quantity) < ORL.LineQuantity