SQL关联3张表查询时返回重复行、统计值翻倍问题

涉及表结构 #

业务共涉及3张核心数据表：

• 分支机构表

[dbo].[Branch](
    [ContractorCode] [int] NOT NULL,
    [BranchNumber] [int] NOT NULL,
    [BranchName] [varchar](50) NOT NULL,
    ...
    [HealthBoardID] [int] NULL
)

• 待盘点记录表

[dbo].[OutstandingCount](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [EnteredOn] [datetime2](7) NOT NULL,    
    [Branch] [varchar](50) NOT NULL,
    [productGroup] [int] NULL,  
    [Product] [nvarchar](100) NOT NULL, 
    ...
    [Satisfied] [bit] NOT NULL
)

• 盘点目标表

[dbo].[TargetLineCount](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Branch] [int] NOT NULL,
    [ProductGroup] [nvarchar](10) NOT NULL,
    [TargetCount] [int] NOT NULL,
    ...
)

初始表关系认知 #

• Branch表与OutstandingCount表为1对多关系：1个分支机构对应多条待盘点记录
• 初始认为Branch表与TargetLineCount表为1对1关系，实际为1个分支机构对应多条不同商品组的目标记录

统计需求 #

按分支机构维度输出两类统计结果：

1. 待盘点商品数：OutstandingCount表中Satisfied = 0的记录总数，按商品组拆分统计
2. 每日新增盘点目标数：由分支机构与商品组共同决定的固定目标值

初始查询与异常表现 #

初始编写的查询语句如下：

SELECT b.BranchName,
SUM(CASE WHEN o.ProductGroup = 2 THEN 1 ELSE 0 END) AS [NHS to count],
MAX(CASE WHEN t.ProductGroup = 'DISP' THEN t.TargetCount ELSE 0 END) AS [NHS daily count],
SUM(CASE WHEN o.ProductGroup = 1 THEN 1 ELSE 0 END) AS [OTC to count],
MAX(CASE WHEN t.ProductGroup = 'OTC' THEN t.TargetCount ELSE 0 END) AS [OTC daily count]
FROM OutstandingCount o
INNER JOIN Branch b ON o.Branch = b.BranchName
INNER JOIN TargetLineCount t ON t.Branch = b.BranchNumber
WHERE o.Satisfied = 0 AND o.EnteredOn < '2022-07-01'
GROUP BY b.BranchNumber,b.BranchName
ORDER BY b.BranchNumber

查询结果中目标计数返回正确：分支1的[NHS/OTC daily count]为7，分支2为8；但[NHS/OTC to count]统计值翻倍：分支1正确值应为65，实际返回130，分支2正确值应为7，实际返回14。
单独查询任意单表做统计时结果均正确，仅在单条查询中同时关联多表获取所有信息时出现异常。

样例数据 #

Branch表样例 #

OutstandingCount表样例 #

TargetLineCount表样例 #

期望返回结果 #

结果说明：Branch1无已完成盘点项，ProductGroup1待计数为2、ProductGroup2待计数为1；Branch2有1项已完成盘点，ProductGroup1待计数为2、ProductGroup2待计数为1。TargetLineCount表中每个分支的每个ProductGroup仅对应一个目标值。

问题根因 #

SQL多表JOIN的底层逻辑是按ON条件做笛卡尔积匹配：所有满足关联条件的行都会两两组合返回。
初始查询关联TargetLineCount时仅用分支机构编号作为关联条件，而每个分支机构在TargetLineCount表中对应2条不同商品组的记录，这就导致每1条OutstandingCount的待盘点记录，会和当前分支下2条TargetLineCount记录匹配生成2行结果，最终用SUM统计待盘点数时，数值就会变成实际值的2倍。
目标值统计未出错，是因为使用了MAX聚合取对应商品组的目标值，重复生成的匹配行不会改变MAX的取值结果。

解决方案 #

补全关联维度，通过商品组建立待盘点记录和目标记录的精确匹配，避免无意义的笛卡尔积。需要新增商品组映射表补全连接逻辑，修正后的关联语句如下：

INNER JOIN ProductGroup p ON p.Branch = b.ContractorCode AND p.ProdGroupID = o.productGroup
INNER JOIN TargetLineCount t ON t.Branch = b.BranchNumber AND t.ProductGroup = p.Description

修正后每一条待盘点记录只会匹配到对应商品组的唯一一条目标记录，不会再生成重复行，待盘点数的统计结果即可恢复正常。

内容的提问来源于stack exchange，提问作者Colin-G-Davidson