基于子查询数量的结果集
提问于 2019-03-11 17:11:44
我有两个试图组合的查询；基本上第一个查询找出特定产品的发货数量
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'
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-03-11 20:13:17
我很确定这只是一个典型的聚合查询。就像这样。请注意，我将别名更改为与表相关的名称。使用通用别名真的很有挑战性。https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-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子句。
只需在group by后面添加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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55107025
复制相关文章
点击加载更多
腾讯云开发者
