完全外连接不返回不匹配的连接字段值 SQL SERVER 2005

Question

我使用下面的代码来计算给定月份（本例中为九月）按保险范围（汽车）赚取的保费。该代码的作用是减去截至 9 月 30 日的所有时间所赚取的总保费减去截至 8 月 31 日为止的所有时间所赚取的保费总额，以便返回仅在 9 月份期间所赚取的保费。

结果如下所示：

eprem        coveragecode  
0.0211       AD  
277970.8291  BI  
245700.6741  COLL  
86997.5694   COMP  
85.0083      CustomParts  
848.7873     Death  
0.00         ECL  
0.00         GPIP  
692.3802     Income  
2410.5513    MED  
267670.1099  PD  
387628.504   PIP  
26.8767      PU  
11736.2762   Rental  
4304.3367    Towing   
4211.2574    UIMBI  
19804.8964   UMBI  
15145.3211   UMPD  

---

当我将所有承保范围的保费加起来时，我得到 1325233.399，这是 9 月份赚取的正确保费金额。

现在我想做的是在结果中添加邮政编码字段。如果我运行下面的代码而没有在代码中的任何位置指定邮政编码列，那么我得到的上述结果是正确的，但是当我添加邮政编码列时，我返回了预期的数千行，但是当我将总保费加起来时，我得到了1323608.401 而不是正确的总数 1325233.399。我相信正在发生的事情是有一定的覆盖范围和范围。一个月（派生表）中存在的邮政编码组合（这是我加入 2 个派生表的原因），并在另一个月中进行注释。当我进行右连接时，我得到的行数与进行左连接时返回的行数不同，但是当我将所有溢价相加时，它始终等于 1323608.401 而不是 1325233.399 。我认为解决我的问题的方法是使用完整的外部连接，从而返回所有结果（即使是那些不匹配的结果），这样我的总保费将总计为 1325233.399，但无论哪种类型的连接，我仍然只得到 1323608.401我尝试使用。有谁知道这里会发生什么吗？？？任何帮助将不胜感激。谢谢！

    SELECT   sept.eprem-aug.eprem as eprem , sept.coveragecode, zipcode 
from

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate = '09-30-2011' 
and decpagetypecode != 'x'
GROUP BY evaluationdate ,coveragecode, zipcode

) as sept full outer join 

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate = CONVERT(DATETIME, DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,'09-30-2011'),0)), 102)  
and decpagetypecode != 'x'
GROUP BY evaluationdate ,coveragecode, zipcode

) as aug on sept.coveragecode = aug.coveragecode and sept.zipcode = aug.zipcode
where sept.coveragecode is not null and sept.coveragecode <> ''
order by coveragecode, sept.evaluationdate

Answer 1

我所要做的就是更改第一行代码，将任何空溢价值转换为 0。

SELECT   isnull(sept.eprem,0)-isnull(aug.eprem,0) as eprem , sept.coveragecode, zipcode 
from

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate = '09-30-2011' 
and decpagetypecode != 'x'
GROUP BY evaluationdate ,coveragecode, zipcode

) as sept full outer join 

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate = CONVERT(DATETIME, DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,'09-30-2011'),0)), 102)  
and decpagetypecode != 'x'
GROUP BY evaluationdate ,coveragecode, zipcode

) as aug on sept.coveragecode = aug.coveragecode and sept.zipcode = aug.zipcode
where sept.coveragecode is not null and sept.coveragecode <> ''
order by coveragecode, sept.evaluationdate

Answer 2

您的 FULL OUTER JOIN 条件应如下所示：

SELECT   sept.eprem-aug.eprem as eprem , sept.coveragecode, zipcode 
from

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate = '09-30-2011' 
and decpagetypecode != 'x'
-- Migrated WHERE Condition
AND COALESCE(eprem.coveragecode, '') > ''
GROUP BY evaluationdate ,coveragecode, zipcode

) as sept FULL OUTER join 

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate = CONVERT(DATETIME, DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,'09-30-2011'),0)), 102)  
and decpagetypecode != 'x'
GROUP BY evaluationdate ,coveragecode, zipcode

) as aug on sept.coveragecode = aug.coveragecode and sept.zipcode = aug.zipcode
/*
|| See SEPT derived table for elimination of NULL and '' coverage codes
|| where sept.coveragecode is not null and sept.coveragecode <> ''
*/
order by coveragecode, sept.evaluationdate

需要考虑的一件事是，当您使用 OUTER JOIN 时，WHERE 条件可能会导致优化器将 JOIN 重写为 INNER JOIN。

希望这有帮助。