SQL Server多表关联后NumberofLogins计数异常问题排查与修正

在SQL Server同一数据库中，通过SkillTargetID关联t_Agent_Event_Detail和t_Agent_Skill_Group_Interval两张表提取数据时，单独查询每张表结果正常，但关联后NumberofLogins字段返回的是t_Agent_Skill_Group_Interval表对应SkillTargetID的行数，而非t_Agent_Event_Detail表中Event=1的计数。

原关联查询语句 #

select t_Agent_Event_Detail.SkillTargetID
,NumberofLogins = count(Event)
,LoggedIn = sum(t_Agent_Skill_Group_Interval.LoggedOnTime)
,NotReady = sum(t_Agent_Skill_Group_Interval.NotReadyTime)
,TalkTime = sum(t_Agent_Skill_Group_Interval.HandledCallsTalkTime)
,WrapUp = sum(t_Agent_Skill_Group_Interval.WorkReadyTime)

FROM
t_Agent_Event_Detail

LEFT JOIN
t_Agent_Skill_Group_Interval 
ON t_Agent_Event_Detail.SkillTargetID = t_Agent_Skill_Group_Interval.SkillTargetID 

WHERE
t_Agent_Event_Detail.DateTime > '2023-06-06'
and t_Agent_Event_Detail.Event = 1
and t_Agent_Event_Detail.SkillTargetID IN (5124,7813)
and t_Agent_Skill_Group_Interval.DateTime > '2023-06-06'
and t_Agent_Skill_Group_Interval.SkillTargetID IN (5124,7813)

GROUP BY t_Agent_Event_Detail.SkillTargetID, 
t_Agent_Skill_Group_Interval.SkillTargetID

关联查询错误结果 #

单独查询的预期结果 #

t_Agent_Skill_Group_Interval查询结果 #

select
SkillTargetID
,LoggedInTime = sum(LoggedOnTime)
,NotReady = sum(NotReadyTime)
,TalkTime = sum(HandledCallsTalkTime)
,WrapUp = sum(WorkReadyTime) 
From t_Agent_Skill_Group_Interval
where DateTime > '2023-06-06' and SkillTargetID IN (5124,7813)
GROUP BY SkillTargetID

t_Agent_Event_Detail查询结果 #

select
       SkillTargetID
      ,NumberofLogins = count(Event)
from
t_Agent_Event_Detail
where
DateTime > '2023-06-06' and Event = 1
GROUP BY SkillTargetID

示例数据 #

t_Agent_Event_Detail表 #

t_Agent_Skill_Group_Interval表 #

1. 笛卡尔积导致重复统计：两张表通过SkillTargetID关联时，t_Agent_Event_Detail中每个符合条件的Event=1记录，会和t_Agent_Skill_Group_Interval中所有同SkillTargetID的记录配对，产生多行重复数据。比如SkillTargetID=5124在t_Agent_Event_Detail中有1条Event=1的记录，在t_Agent_Skill_Group_Interval中有5条记录，关联后就会生成5条重复行，最终count(Event)统计的是关联后的总行数，而非原表的登录次数。
2. LEFT JOIN被转为INNER JOIN：WHERE子句中加入了t_Agent_Skill_Group_Interval.DateTime > '2023-06-06'的条件，过滤掉了右表无匹配的行，使得原本的LEFT JOIN等价于INNER JOIN，但这不是计数错误的核心原因。

方案一：先分别聚合两张表，再关联 #

先对两张表各自按SkillTargetID聚合计算所需指标，再将聚合后的结果关联，避免笛卡尔积问题：

-- 先聚合t_Agent_Event_Detail的登录次数
WITH EventAgg AS (
    SELECT
        SkillTargetID,
        NumberofLogins = COUNT(Event)
    FROM t_Agent_Event_Detail
    WHERE DateTime > '2023-06-06' AND Event = 1 AND SkillTargetID IN (5124,7813)
    GROUP BY SkillTargetID
),
-- 再聚合t_Agent_Skill_Group_Interval的时长指标
SkillAgg AS (
    SELECT
        SkillTargetID,
        LoggedIn = SUM(LoggedOnTime),
        NotReady = SUM(NotReadyTime),
        TalkTime = SUM(HandledCallsTalkTime),
        WrapUp = SUM(WorkReadyTime)
    FROM t_Agent_Skill_Group_Interval
    WHERE DateTime > '2023-06-06' AND SkillTargetID IN (5124,7813)
    GROUP BY SkillTargetID
)
-- 关联两个聚合结果
SELECT
    COALESCE(e.SkillTargetID, s.SkillTargetID) AS SkillTargetID,
    e.NumberofLogins,
    s.LoggedIn,
    s.NotReady,
    s.TalkTime,
    s.WrapUp
FROM EventAgg e
FULL JOIN SkillAgg s ON e.SkillTargetID = s.SkillTargetID;

方案二：使用子查询计算登录次数 #

在主查询中通过子查询直接获取每个SkillTargetID的登录次数，避免关联后的重复统计：

SELECT
    t.SkillTargetID,
    NumberofLogins = (
        SELECT COUNT(Event)
        FROM t_Agent_Event_Detail
        WHERE SkillTargetID = t.SkillTargetID AND DateTime > '2023-06-06' AND Event = 1
    ),
    LoggedIn = SUM(t.LoggedOnTime),
    NotReady = SUM(t.NotReadyTime),
    TalkTime = SUM(t.HandledCallsTalkTime),
    WrapUp = SUM(t.WorkReadyTime)
FROM t_Agent_Skill_Group_Interval t
WHERE t.DateTime > '2023-06-06' AND t.SkillTargetID IN (5124,7813)
GROUP BY t.SkillTargetID;

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