基于COUNT()的多查询列合并问题求助

核心问题 #

你的SQL存在三个关键问题导致结果不符合预期：

• 使用逗号连接多个子查询会生成笛卡尔积，未按用户维度关联，导致数据交叉填充错误
• 统计结果中的NULL值未替换为0，无法匹配空值显示为0的需求
• Title_LOGS的UNION ALL会将同一用户的创建和修改操作统计结果分开返回，后续关联时会重复计算

修正后的SQL语句 #

SELECT 
    BU.text AS [Column A],
    ISNULL(C.Chapters, 0) AS [Column B_Chapters],
    ISNULL(T.Titles, 0) AS [Column B_Titles],
    ISNULL(T.Epilogues, 0) AS [Column C],
    ISNULL(T.Prologues, 0) AS [Column D],
    ISNULL(A.Authors, 0) AS [Column B_Authors]
FROM 
    BookUsers BU
LEFT JOIN (
    -- 统计章节数量
    SELECT 
        UploadBy AS UserId,
        COUNT(ChapName) AS Chapters
    FROM Chapters 
    WHERE UploadDate BETWEEN '2023-01-01' AND '2023-07-31'
        AND UploadBy IN ('User 1','User 2','User 3','User 4')
    GROUP BY UploadBy
) C ON BU.id = C.UserId
LEFT JOIN (
    -- 合并标题的创建/修改统计，避免重复计算
    SELECT 
        UserId,
        SUM(Titles) AS Titles,
        SUM(Epilogues) AS Epilogues,
        SUM(Prologues) AS Prologues
    FROM (
        SELECT 
            CreatedBy AS UserId,
            COUNT(Titles) AS Titles,
            COUNT(Epilogues) AS Epilogues,
            COUNT(Prologues) AS Prologues
        FROM Title_LOGS 
        WHERE CreatedDate BETWEEN '2023-01-01' AND '2023-07-31'
            AND CreatedBy IN ('User 1','User 2','User 3','User 4')
        GROUP BY CreatedBy
        UNION ALL
        SELECT 
            ModifiedBy AS UserId,
            COUNT(Titles) AS Titles,
            COUNT(Epilogues) AS Epilogues,
            COUNT(Prologues) AS Prologues
        FROM Title_LOGS 
        WHERE ModifiedDate BETWEEN '2023-01-01' AND '2023-07-31'
            AND ModifiedBy IN ('User 1','User 2','User 3','User 4')
        GROUP BY ModifiedBy
    ) Tmp
    GROUP BY UserId
) T ON BU.id = T.UserId
LEFT JOIN (
    -- 统计作者数量
    SELECT 
        CreatedBy AS UserId,
        COUNT(Authors) AS Authors
    FROM Authors 
    WHERE CreatedDate BETWEEN '2023-01-01' AND '2023-07-31'
        AND CreatedBy IN ('User 1','User 2','User 3','User 4')
    GROUP BY CreatedBy
) A ON BU.id = A.UserId
WHERE BU.id IN ('User 1','User 2','User 3','User 4')
ORDER BY BU.text;

关键调整说明 #

1. LEFT JOIN关联：以BookUsers为主表，通过用户ID关联各统计子查询，确保每个用户的统计结果正确匹配，避免笛卡尔积
2. NULL值转0：用ISNULL()（SQL Server）或COALESCE()（通用SQL）将缺失的统计值转为0，匹配期望结果
3. 修复重复统计：对Title_LOGS的UNION ALL结果再次聚合，合并同一用户的创建/修改操作统计，避免关联时出现重复数据
4. 唯一列别名：给多个Column B对应统计列起不同别名，避免语法错误的同时清晰区分统计维度

结果对应关系 #

• Column B_Chapters对应表格1的Column B
• Column B_Titles对应表格2的Column B
• Column B_Authors对应表格3的Column B

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