查询语句如下: SELECT abc.*, ISNULL(abd.productNum, 0) - ISNULL(abd.productDestineNum, 0) AS useNum FROM (SELECT r.productID, r.productIntCode, r.productCode, r.productName, SUM(r.productNum) AS productNum FROM (SELECT productID, productIntCode, productCode, productName, SUM(productNum) productNum FROM viewSaleProduct WHERE productID IN (SELECT DISTINCT packID FROM VIEWsaleOrderProductSplitPack WHERE saleOrderID = 1005 AND ((CHARINDEX(';5', productFlow) > 0) OR (CHARINDEX(';5', componentFlow) > 0) OR (CHARINDEX(',5', ',' + productPackDep) > 0) OR (CHARINDEX(';5', partsFlow) > 0))) AND saleOrderID = 1005 GROUP BY productID, productIntCode, productCode, productName UNION SELECT DISTINCT productID, productIntCode, productCode, productName, productNum FROM VIEWsaleOrderSplitPack WHERE saleOrderID = 1005 AND ((CHARINDEX(';5', productFlow) > 0) OR (CHARINDEX(';5', componentFlow) > 0) OR (CHARINDEX(',5', ',' + productPackDep) > 0) OR (CHARINDEX(';5', partsFlow) > 0))) AS r GROUP BY r.productID, r.productIntCode, r.productCode, r.productName) abc LEFT OUTER JOIN (SELECT * FROM storewholeproduct WHERE WholeHOuseID = 2) abd ON abc.productID = abd.productID
SELECT abc.*, ISNULL(abd.productNum, 0) - ISNULL(abd.productDestineNum, 0)
AS useNum
FROM (SELECT r.productID, r.productIntCode, r.productCode, r.productName,
SUM(r.productNum) AS productNum
FROM (SELECT productID, productIntCode, productCode, productName,
SUM(productNum) productNum
FROM viewSaleProduct
WHERE productID IN
(SELECT DISTINCT packID
FROM VIEWsaleOrderProductSplitPack
WHERE saleOrderID = 1005 AND ((CHARINDEX(';5', productFlow)
> 0) OR
(CHARINDEX(';5', componentFlow) > 0) OR
(CHARINDEX(',5', ',' + productPackDep) > 0) OR
(CHARINDEX(';5', partsFlow) > 0))) AND
saleOrderID = 1005
GROUP BY productID, productIntCode, productCode, productName
UNION
SELECT DISTINCT
productID, productIntCode, productCode, productName,
productNum
FROM VIEWsaleOrderSplitPack
WHERE saleOrderID = 1005 AND ((CHARINDEX(';5', productFlow) > 0) OR
(CHARINDEX(';5', componentFlow) > 0) OR
(CHARINDEX(',5', ',' + productPackDep) > 0) OR
(CHARINDEX(';5', partsFlow) > 0))) AS r
GROUP BY r.productID, r.productIntCode, r.productCode, r.productName)
abc LEFT OUTER JOIN
(SELECT *
FROM storewholeproduct
WHERE WholeHOuseID = 2) abd ON abc.productID = abd.productID