关联7张表的内连接查询结果不准确，求原因分析

数据库结构如图所示。

以下是包含利润计算的查询语句：

SELECT TOP 3 Region.RegionID as Region, 
             Country.CountryName as Country, 
             Segment.SegmentName as Segment, 
             YEAR(SalesOrder.SalesOrderDate) as FinancialYear, 
             ROUND(SUM(SalesOrderLineItem.SalePrice),2) AS YearlySales, 
             ROUND(SUM(SalesOrderLineItem.SalePrice-
  (ProductCost.ManufacturingPrice*SalesOrderLineItem.UnitsSold)),2) AS 
            Profit
    FROM (((((((Country
        INNER JOIN Region ON Country.CountryID= Region.CountryID)
        INNER JOIN Segment ON Region.SegmentID= Segment.SegmentID)
        INNER JOIN SalesRegion ON Region.RegionID= 
        SalesRegion.RegionID)
        INNER JOIN SalesOrder ON SalesRegion.SalesRegionID= 
        SalesOrder.SalesRegionID)
        INNER JOIN SalesOrderLineItem ON SalesOrder.SalesOrderID= 
        SalesOrderLineItem.SalesOrderID)
        INNER JOIN Product ON SalesOrderLineItem.ProductID= 
        Product.ProductID)
        INNER JOIN ProductCost ON Product.ProductID= 
        ProductCost.ProductID)

    GROUP BY Region.RegionID, Country.CountryName, Segment.SegmentName, 
         YEAR(SalesOrder.SalesOrderDate)
    ORDER BY YEAR(SalesOrder.SalesOrderDate) ASC, Country.CountryName ASC, 
         Region.RegionID ASC;

执行后结果：

移除利润相关的连接和字段后，查询语句如下：

SELECT TOP 3 Region.RegionID as Region, Country.CountryName as Country, Segment.SegmentName as Segment, YEAR(SalesOrder.SalesOrderDate) as FinancialYear, ROUND(SUM(SalesOrderLineItem.SalePrice),2) AS YearlySales
FROM (((((Country
INNER JOIN Region ON Country.CountryID= Region.CountryID)
INNER JOIN Segment ON Region.SegmentID= Segment.SegmentID)
INNER JOIN SalesRegion ON Region.RegionID= SalesRegion.RegionID)
INNER JOIN SalesOrder ON SalesRegion.SalesRegionID= SalesOrder.SalesRegionID)
INNER JOIN SalesOrderLineItem ON SalesOrder.SalesOrderID= SalesOrderLineItem.SalesOrderID)

GROUP BY Region.RegionID, Country.CountryName, Segment.SegmentName, YEAR(SalesOrder.SalesOrderDate)
ORDER BY YEAR(SalesOrder.SalesOrderDate) ASC, Country.CountryName ASC, Region.RegionID ASC;

执行后结果：

为什么两次查询的YearlySales数值会出现差异？

核心原因 #

问题出在ProductCost表与其他表的连接逻辑上：ProductCost表中同一个ProductID对应了多条成本记录（比如不同时间的成本版本）。当用Product.ProductID = ProductCost.ProductID做内连接时，每一条SalesOrderLineItem记录会和该产品对应的所有ProductCost记录匹配，导致原本的销售记录被重复复制N次（N为该产品在ProductCost中的记录数）。

后续执行SUM(SalesOrderLineItem.SalePrice)时，这些重复的销售记录会被多次累加，最终导致YearlySales的数值被放大（从结果看是放大了5倍左右，说明对应产品在ProductCost中平均有5条重复记录）。

解决方法 #

要修正这个问题，需要确保每个ProductID只关联到一条有效的成本记录，常见的处理方式有两种：

1. 提前聚合ProductCost表
   先对ProductCost按ProductID分组，取该产品的有效成本（比如最新的成本、平均成本，或根据业务规则取特定版本），再和其他表连接：

   SELECT TOP 3 
       Region.RegionID as Region, 
       Country.CountryName as Country, 
       Segment.SegmentName as Segment, 
       YEAR(SalesOrder.SalesOrderDate) as FinancialYear, 
       ROUND(SUM(SalesOrderLineItem.SalePrice),2) AS YearlySales, 
       ROUND(SUM(SalesOrderLineItem.SalePrice - (pc.ManufacturingPrice * SalesOrderLineItem.UnitsSold)),2) AS Profit
   FROM (((((((Country
       INNER JOIN Region ON Country.CountryID= Region.CountryID)
       INNER JOIN Segment ON Region.SegmentID= Segment.SegmentID)
       INNER JOIN SalesRegion ON Region.RegionID= SalesRegion.RegionID)
       INNER JOIN SalesOrder ON SalesRegion.SalesRegionID= SalesOrder.SalesRegionID)
       INNER JOIN SalesOrderLineItem ON SalesOrder.SalesOrderID= SalesOrderLineItem.SalesOrderID)
       INNER JOIN Product ON SalesOrderLineItem.ProductID= Product.ProductID)
       -- 子查询获取每个产品的最新成本（假设CostDate是成本生效日期）
       INNER JOIN (
           SELECT ProductID, ManufacturingPrice
           FROM (
               SELECT 
                   ProductID, 
                   ManufacturingPrice,
                   ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY CostDate DESC) AS rn
               FROM ProductCost
           ) t
           WHERE rn = 1
       ) pc ON Product.ProductID= pc.ProductID)
   GROUP BY Region.RegionID, Country.CountryName, Segment.SegmentName, YEAR(SalesOrder.SalesOrderDate)
   ORDER BY YEAR(SalesOrder.SalesOrderDate) ASC, Country.CountryName ASC, Region.RegionID ASC;
   

2. 在SUM中去重计算（临时方案）
   如果只是临时修正销售额数值，可以用SUM(DISTINCT SalesOrderLineItem.SalePrice)，但这种方法有局限性：如果不同订单行的SalePrice恰好相同，会导致数值被少算，因此更推荐第一种方法。

内容的提问来源于stack exchange，提问作者Testnominiee