如何更正我的查询？ [英] How to correct my query?

问题描述

我有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

但是我的结果是正确的。

你可以告诉我你跟着哪个老人

But my result is correct.
can u tell me which Senior you follow

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