如何在查询结果中包含table2中各doc_id的总记录数？

你需要先单独统计Table2中每个doc_id的总记录数，再将这个统计结果与原查询结果关联，而非直接在原查询中使用COUNT——原查询的COUNT会基于当前连接后的行统计，无法得到Table2全局的doc_id计数。

方法一：子查询关联法 #

SELECT 
    t1.col_a, 
    t2.doc_id, 
    t1.col_c,
    doc_counts.total_count AS count
FROM dbo.table1 t1
LEFT JOIN dbo.table2 t2 ON t1.id = t2.id
LEFT JOIN (
    -- 预统计Table2中每个doc_id的总记录数
    SELECT doc_id, COUNT(*) AS total_count
    FROM dbo.table2
    GROUP BY doc_id
) doc_counts ON t2.doc_id = doc_counts.doc_id
WHERE t1.col_c = 'test'
GROUP BY t1.col_a, t2.doc_id, t1.col_c, doc_counts.total_count

方法二：窗口函数法（适用于SQL Server 2008+等支持窗口函数的版本） #

SELECT DISTINCT
    t1.col_a, 
    t2.doc_id, 
    t1.col_c,
    -- 直接统计Table2中当前doc_id的全局总条数
    COUNT(*) OVER (PARTITION BY t2.doc_id) AS count
FROM dbo.table1 t1
LEFT JOIN dbo.table2 t2 ON t1.id = t2.id
WHERE t1.col_c = 'test'

说明 #

• 方法一通过子查询提前完成Table2的doc_id计数统计，再关联到原查询，确保每个doc_id对应的值是Table2全局的总记录数。
• 方法二利用窗口函数COUNT(*) OVER (PARTITION BY t2.doc_id)，无需额外分组即可计算每个doc_id的全局条数，再通过DISTINCT去重得到目标结果。

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