多表关联计数异常：如何正确关联三张表统计转诊与就诊次数

现有表结构与样例数据 #

WellnessProviders表 #

WellnessReferrals表 #

WellnessVisits表 #

问题描述 #

单独统计每个服务商的转诊次数、就诊次数时结果正常：

• 统计转诊次数的SQL：

SELECT wp.ProviderName, count(wr.AssignedToEmail)
FROM dbo.WellnessProviders wp
LEFT JOIN dbo.WellnessReferrals wr ON wp.ProviderEmail=wr.AssignedToEmail
GROUP BY wp.ProviderName

• 统计就诊次数的SQL：

SELECT wp.ProviderName, count(wv.CheckOutProviderEmail)
FROM dbo.WellnessProviders wp
LEFT JOIN dbo.WellnessVisits wv ON wp.ProviderEmail=wv.CheckOutProviderEmail
GROUP BY wp.ProviderName;

但合并为一次关联查询时，统计结果错误：

SELECT wp.ProviderName, count(wr.AssignedToEmail), count(wv.CheckOutProviderEmail)
FROM dbo.WellnessProviders wp
LEFT JOIN dbo.WellnessReferrals wr
    ON wp.ProviderEmail=wr.AssignedToEmail
LEFT JOIN dbo.WellnessVisits wv
    ON wv.CheckOutProviderEmail=wp.ProviderEmail
GROUP BY wp.ProviderName;

期望的正确结果：

错误原因 #

直接同时左连接WellnessReferrals和WellnessVisits会产生笛卡尔积：比如Steve有1条转诊记录和2条就诊记录，连接后会生成1×2=2条重复记录，导致count(wr.AssignedToEmail)和count(wv.CheckOutProviderEmail)都被错误统计为2，而非正确的1和2。

正确解决方案 #

方案一：先分别聚合子查询，再关联 #

先对转诊和就诊数据按服务商邮箱单独聚合统计，再与服务商表关联，避免笛卡尔积：

SELECT 
    wp.ProviderName,
    COALESCE(r.TotalReferrals, 0) AS TotalReferrals,
    COALESCE(v.TotalCheckOuts, 0) AS TotalCheckOuts
FROM dbo.WellnessProviders wp
LEFT JOIN (
    SELECT AssignedToEmail, COUNT(ReferralNumber) AS TotalReferrals
    FROM dbo.WellnessReferrals
    GROUP BY AssignedToEmail
) r ON wp.ProviderEmail = r.AssignedToEmail
LEFT JOIN (
    SELECT CheckOutProviderEmail, COUNT(VisitNumber) AS TotalCheckOuts
    FROM dbo.WellnessVisits
    GROUP BY CheckOutProviderEmail
) v ON wp.ProviderEmail = v.CheckOutProviderEmail;

方案二：使用COUNT(DISTINCT)去重计数 #

如果保留原连接结构，可通过COUNT(DISTINCT)统计唯一的转诊/就诊编号，避免重复计数：

SELECT 
    wp.ProviderName,
    COUNT(DISTINCT wr.ReferralNumber) AS TotalReferrals,
    COUNT(DISTINCT wv.VisitNumber) AS TotalCheckOuts
FROM dbo.WellnessProviders wp
LEFT JOIN dbo.WellnessReferrals wr 
    ON wp.ProviderEmail = wr.AssignedToEmail
LEFT JOIN dbo.WellnessVisits wv 
    ON wp.ProviderEmail = wv.CheckOutProviderEmail
GROUP BY wp.ProviderName;

注：方案一的性能通常更优，尤其是当转诊和就诊数据量较大时，先聚合再关联能减少中间数据量。

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