来自不同行数的两个表的数据之和

Question

有3个表(SorMaster、SorDetail和InvWarehouse)：

SorMaster:

+------------+
| SalesOrder |
+------------+
|    100     |
|    101     |
|    102     |
+------------+

SorDetail:

+------------+------------+---------------+
| SalesOrder | MStockCode | MBackOrderQty |
+------------+------------+---------------+
|    100     |    PN-1    |       4       |
|    100     |    PN-2    |       9       |
|    100     |    PN-3    |       1       |
|    100     |    PN-4    |       6       |
|    101     |    PN-1    |       6       |
|    101     |    PN-3    |       2       |
|    102     |    PN-2    |      19       |
|    102     |    PN-3    |      14       |
|    102     |    PN-4    |       6       |
|    102     |    PN-5    |       4       |
+------------+------------+---------------+

InvWarehouse:

+------------+-----------+-----------+
| MStockCode | Warehouse | QtyOnHand |
+------------+-----------+-----------+
|    PN-1    |     A     |     1     |
|    PN-2    |     B     |     9     |
|    PN-3    |     A     |     0     |
|    PN-4    |     B     |     1     |
|    PN-1    |     A     |     0     |
|    PN-3    |     B     |     5     |
|    PN-2    |     A     |     9     |
|    PN-3    |     B     |     4     |
|    PN-4    |     A     |     6     |
|    PN-5    |     B     |     0     |
+------------+-----------+-----------+

预期结果：

+------------+-----------------+--------------+
| MStockCode | SumBackOrderQty | SumQtyOnHand |
+------------+-----------------+--------------+
|    PN-1    |       10        |      10      |
|    PN-2    |       28        |       1      |
|    PN-3    |       17        |       5      |
|    PN-4    |       12        |      13      |
|    PN-5    |       11        |       6      |
+------------+-----------------+--------------+

我一直在转圈，看不到尽头。它看起来应该很简单，但我就是不能把头绕着它。显然，SumBackOrderQty被计算为SumQtyOnHand的两倍。到目前为止，我一直在PHP中进行计算，而不是select语句，但希望在可能的情况下稍微清理一下。

当前查询语句是：

SELECT  SorDetail.MStockCode,
    SUM(SorDetail.MBackOrderQty) AS 'SumMBackOrderQty',
    SUM(InvWarehouse.QtyOnHand) AS 'SumQtyOnHand'

FROM    SysproCompanyJ.dbo.SorMaster SorMaster,
    SysproCompanyJ.dbo.SorDetail SorDetail LEFT OUTER JOIN SysproCompanyJ.dbo.InvWarehouse InvWarehouse
    ON SorDetail.MStockCode = InvWarehouse.StockCode


WHERE   SorMaster.SalesOrder = SorDetail.SalesOrder
    AND SorMaster.ActiveFlag != 'N'
    AND SorDetail.MBackOrderQty > '0'
    AND SorDetail.MPrice > '0'

GROUP BY SorDetail.MStockCode

ORDER BY    SorDetail.MStockCode ASC

Answer 1

在不提供完整图片的情况下，从RDBMS、数据库模式、您试图解决的问题的描述以及与上述问题相匹配的数据示例来看，以下只是基于Barmar的评论的解决方案可能是什么样子的说明：

SELECT SD.MStockCode, 
       SD.SumBackOrderQty, 
       IW.SumQtyOnHand
FROM (SELECT MStockCode, 
             SUM(MBackOrderQty) AS `SumBackOrderQty`
      FROM SorDetail
           JOIN SorMaster ON SorDetail.SalesOrder=SorMaster.SalesOrder
      WHERE SorMaster.ActiveFlag != 'N'
            AND SorDetail.MBackOrderQty > 0
            AND SorDetail.MPrice > 0
      GROUP BY MStockCode) AS SD
     LEFT JOIN (SELECT MStockCode, 
                       SUM(QtyOnHand) AS `SumQtyOnHand`
                FROM InvWarehouse
                GROUP BY MStockCode) AS IW ON SD.MStockCode=IW.MStockCode
ORDER BY SD.MStockCode;

Answer 2

这里有一个方法：

select MStockCode, 
(select sum(MBackOrderQty) from sorDetail as T2
where T2.MStockCode = T1.MStockCode ) as SumBackOrderQty,
(select sum(QtyOnHand) from invWarehouse as T3
where T3.MStockCode = T1.MStockCode ) as SumQtyOnHand 
from 
(
select mstockcode from sorDetail
union
select mstockcode from invWarehouse
) as T1

在小提琴里：http://sqlfiddle.com/#!9/fdaca/6

尽管我的SumQtyOnHand值与您的不匹配(正如@Gordon所指出的)。