如何更正我的查询？

本文介绍了如何更正我的查询？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有3张公司，购买和销售表。

I have 3 tables Company, Purchase and Sales.

CREATE table tblCompany (CompanyID int, Company varchar(50))

INSERT INTO tblCompany (CompanyID, Company) VALUES (1, 'ABC')

CREATE table tblPurchases (TranID int, CompanyID int, PurQty int)

INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (1, 1, 774)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (2, 1, 351)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (3, 1, 600)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (4, 1, 500)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (5, 1, 501)

CREATE table tblSales (TranID int, CompanyID int, SaleQty int)

INSERT INTO tblSales (TranID, CompanyID, SaleQty) VALUES (1, 1, 2726)

我想要正确的数量余额。

CompanyID，TPurQty，TSaleQty，BalanceQty

1,2726,2726,0

但它显示如下：
1,2726,13630，-10904

查询中的错误在哪里？

我尝试过：

I want correct balance of quantities.

CompanyID, TPurQty, TSaleQty, BalanceQty
1 , 2726 , 2726 , 0

But It displays like this:
1 , 2726 ,13630 ,-10904

Where is the error in query?

What I have tried:

SELECT C.CompanyID, ISNULL(SUM(P.PurQty),0) TPurQty, ISNULL(SUM(S.SaleQty),0) TSaleQty
	, (ISNULL(SUM(P.PurQty),0) - ISNULL(SUM(S.SaleQty),0)) BalanceQty   FROM tblCompany C
	FULL OUTER JOIN tblPurchases P ON C.CompanyID = P.CompanyID
		FULL OUTER JOIN tblSales S ON P.CompanyID = S.CompanyID
GROUP by C.CompanyID

推荐答案

SELECT  C.CompanyID
       ,P.PurQty             AS TPurQty
       ,S.SaleQty            AS TSaleQty
       ,P.PurQty- S.SaleQty  AS BalanceQty  

      FROM tblCompany C
             inner JOIN (
     select CompanyID,
            sum(ISNULL(PurQty,0)) as PurQty 
      FROM tblPurchases GROUP BY CompanyID
                        )AS P ON C.CompanyID = P.CompanyID

	     inner JOIN (
    select CompanyID,
        SUM(ISNULL(SaleQty,0)) AS SaleQty
   FROM tblSales GROUP BY CompanyID 
                       ) AS S  ON P.CompanyID = S.CompanyID

SELECT C.CompanyID, ISNULL(SUM(P.PurQty),0) TPurQty, ISNULL(S.SaleQty,0) TSaleQty
	, (ISNULL(SUM(P.PurQty),0) - ISNULL(S.SaleQty,0)) BalanceQty   FROM #tblCompany C
	FULL OUTER JOIN #tblPurchases P ON C.CompanyID = P.CompanyID
		FULL OUTER JOIN #tblSales S ON P.CompanyID = S.CompanyID
GROUP by C.CompanyID,s.SaleQty

这篇关于如何更正我的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！