SQL如何关联两表统计各分组下匹配记录的动作总次数

基础信息 #

表结构 #

• 表1（NamedRecord）：包含ID、Name、Type、Start、End字段，Name字段以TR/DR/FR开头分别对应Town Row、Doctor Row、Fruit Row三个分类。
• 表2：包含SessID、Action字段，其中SessID与表1的ID字段关联匹配，存储各会话对应的动作记录。

原有查询逻辑 #

原有SQL用于筛选Type='A'且符合日期范围（Start大于等于当月月初、End小于等于当前日期+1天）的记录，按三个Name分类分组统计各分组记录总数NameTotals。注意原代码存在语法问题：计算当月月初的DATEADD函数缺少闭合右括号，原代码如下：

Select
    CASE
        WHEN T1.Name LIKE 'TR%' THEN 'Town Row'
        WHEN T1.Name LIKE 'DR%' THEN 'Doctor Row'
        WHEN T1.Name LIKE 'FR%' THEN 'Fruit Row'
    END AS Name
    , COUNT(*) AS 'NameTotals'
From
    NamedRecord T1
Where
    T1.Type = 'A'
    AND
    T1.Start >= DATEADD(MONTH, DATEDIFF(Month,0,Getdate(),0) -- 此处缺少右括号
    AND
    T1.End <= DATEADD(Day,1,Getdate())
Group by
    (
        CASE
            WHEN T1.Name LIKE 'TR%' THEN 'Town Row'
            WHEN T1.Name LIKE 'DR%' THEN 'Doctor Row'
            WHEN T1.Name LIKE 'FR%' THEN 'Fruit Row'
        END
    )
Order By
    Name

该查询可正确返回各分组记录总数：Town Row为4、Doctor Row为4、Fruit Row为3。

改造要求 #

新增WhatHappTotals统计列：通过表2的SessID关联匹配表1的ID，映射到对应Name分类后，统计每个分类下关联的所有Action动作总数量。最终返回结果需包含Name、NameTotals、WhatHappTotals三列，三个分类的动作总数分别为6、8、6，结果按Name排序。 #

改写实现 #

核心注意点：两表关联后，单条NamedRecord记录会对应多条表2的动作记录，如果直接COUNT(*)会导致NameTotals计数虚高，需要通过提前聚合或去重计数规避这个问题。

推荐写法（性能更优，无计数错误） #

通过CTE提前筛选符合条件的表1记录、提前聚合动作统计值，避免关联后数据膨胀：

WITH FilteredNamedRecord AS (
    -- 提前筛选符合日期、类型条件的记录，统一做分类映射，避免重复编写CASE逻辑
    SELECT
        ID,
        CASE
            WHEN Name LIKE 'TR%' THEN 'Town Row'
            WHEN Name LIKE 'DR%' THEN 'Doctor Row'
            WHEN Name LIKE 'FR%' THEN 'Fruit Row'
        END AS CategoryName
    FROM NamedRecord
    WHERE
        Type = 'A'
        -- 修复原SQL缺失的右括号，正确计算当月月初日期
        AND Start >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
        AND End <= DATEADD(DAY, 1, GETDATE())
),
CategoryActionCount AS (
    -- 提前按分类聚合动作总数，避免关联后重复计数
    SELECT
        fnr.CategoryName,
        COUNT(t2.Action) AS WhatHappTotals
    FROM FilteredNamedRecord fnr
    LEFT JOIN 表2 t2 ON fnr.ID = t2.SessID
    GROUP BY fnr.CategoryName
)
SELECT
    fnr.CategoryName AS Name,
    COUNT(fnr.ID) AS NameTotals,
    ISNULL(cac.WhatHappTotals, 0) AS WhatHappTotals
FROM FilteredNamedRecord fnr
LEFT JOIN CategoryActionCount cac ON fnr.CategoryName = cac.CategoryName
GROUP BY fnr.CategoryName, cac.WhatHappTotals
ORDER BY Name

简化写法（代码量更少，适合数据量小的场景） #

直接左连两表，统计记录总数时使用COUNT(DISTINCT)对表1主键去重，规避关联膨胀问题：

SELECT
    CASE
        WHEN T1.Name LIKE 'TR%' THEN 'Town Row'
        WHEN T1.Name LIKE 'DR%' THEN 'Doctor Row'
        WHEN T1.Name LIKE 'FR%' THEN 'Fruit Row'
    END AS Name,
    COUNT(DISTINCT T1.ID) AS NameTotals, -- 主键去重，避免关联多动作导致计数偏大
    COUNT(T2.Action) AS WhatHappTotals
FROM NamedRecord T1
LEFT JOIN 表2 T2 ON T1.ID = T2.SessID
WHERE
    T1.Type = 'A'
    AND T1.Start >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) -- 修复原SQL括号问题
    AND T1.End <= DATEADD(DAY, 1, GETDATE())
GROUP BY
    CASE
        WHEN T1.Name LIKE 'TR%' THEN 'Town Row'
        WHEN T1.Name LIKE 'DR%' THEN 'Doctor Row'
        WHEN T1.Name LIKE 'FR%' THEN 'Fruit Row'
    END
ORDER BY Name

返回结果示例 #

两种写法执行后均会返回符合预期的结果：

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