减法运算返回错误结果

Question

SELECT 
    TOB.SchoolID,
    SUM (TOB.Rice)- SUM(TDMD.Rice)
FROM  tblOpeningBalance TOB 
JOIN  tblDailyMidDayDetails TDMD 
    ON TOB.SchoolID = TDMD.SchoolID
WHERE  TOB.Date<=CONVERT(datetime,'19/06/2014',103) 
GROUP BY TOB.SchoolID;

这是我得到错误结果的查询。

要获得我想要单独减去的值，查询是..。

SELECT 
    DISTINCT SchoolID, 
    SUM (Rice) AS Rice
FROM  tblOpeningBalance 
WHERE  Date <= CONVERT(datetime,'19/06/2014',103) 
GROUP BY SchoolID;

第二个查询：

SELECT 
    SchoolID,
    SUM(Rice) AS Rice 
FROM tblDailyMidDayDetails 
WHERE Date<=CONVERT(datetime,'19/06/2014',103)
GROUP BY SchoolID;

这两个查询返回真正的结果，但是当试图通过加入获得减去的结果时，我得到了错误的值。

可能是我做错了错误的观念。

请指引我！

Answer 1

只需将现有查询(DISTINCT除外)保留为子查询：

SELECT ob.SchoolID,
       ob.Rice - dmdd.Rice
FROM (
  Select SchoolID, 
  SUM (Rice) AS Rice
  FROM tblOpeningBalance 
  Where 
  Date<='20140619' 
  GROUP BY SchoolID) ob
     INNER JOIN
(
  SELECT SchoolID,SUM(Rice) AS Rice
  FROM tblDailyMidDayDetails
  WHERE Date<='20140619' 
GROUP BY SchoolID) dmdd
     ON
         ob.SchoolID = dmdd.SchoolID

这确保连接的每个SchoolID只有一行，这避免了在聚合某些值之前重复它们。

我猜您有一个问题，因为当前两个表中都存在相同SchoolID的多个行。你得到的问题在这里得到了说明。假设表1有以下数据：

SchoolID    Column1     Rice
1           abc         10
1           def         20

表2的数据如下：

SchoolID    Column2     Rice
1           ghi         30
1           jkl         40

在计算聚合之前执行联接的结果是创建以下行：

SchoolID    Column1     Rice    SchoolID     Column2     Rice
1           abc         10      1            ghi         30
1           abc         10      1            jkl         40
1           def         20      1            ghi         30
1           jkl         20      1            jkl         40

希望您能够理解为什么针对这个结果集的计算聚合会产生错误的结果。

Answer 2

我们也可以这样做。

;WITH CTE AS  
    (Select SchoolID As SchoolID, 
  SUM (Rice) AS Rice
  FROM tblOpeningBalance 
  Where 
  Date<='20140619' 
  GROUP BY SchoolID)
,CTE2 AS 
  ( SELECT SchoolID As SchoolID,SUM(Rice) AS Rice
  FROM tblDailyMidDayDetails
  WHERE Date<='20140619' 
    GROUP BY SchoolID)

Select C.SchoolID,C.Rice - CC.Rice As RIce
     FROM CTE C
INNER JOIN CTE2 CC
    ON c.SchoolID = Cc.SchoolID
GROUP BY C.SchoolID