MySQL关联带SUM逻辑的子查询仅返回一行结果的原因咨询

一、仅返回一行的原因 #

主查询中使用了SUM()聚合函数，但未对u.id和u.name添加GROUP BY分组逻辑。MySQL会将所有匹配记录合并为一行计算全局总和，而非按单个用户维度聚合结果。

二、修正后的SQL语句 #

针对问题做三处核心调整，同时保留deletedOn IS NULL过滤逻辑：

SELECT
    u.id AS id,
    u.name AS name,
    SUM(IFNULL(p.totalSeen, 0) + IFNULL(q.totalSeen, 0)) AS totalSeen,
    SUM(IFNULL(p.totalLikes, 0) + IFNULL(q.totalLikes, 0)) AS totalLikes,
    SUM(IFNULL(p.totalShares, 0) + IFNULL(q.totalShares, 0)) AS totalShares,
    SUM(IFNULL(p.totalBookmarks, 0) + IFNULL(q.totalBookmarks, 0)) AS totalBookmarks,
    SUM(IFNULL(p.totalDownloads, 0)) AS totalDownloads,
    SUM(IFNULL(p.totalFeedbacks, 0)) AS totalFeedbacks
FROM users u
-- 可选：主表过滤已删除用户，与子查询逻辑保持一致
WHERE u.deletedOn IS NULL
LEFT JOIN (
    SELECT
        pu.id AS id,
        SUM(IF(pe.event = 'SEEN', 1, 0)) AS totalSeen,
        SUM(IF(pe.event = 'LIKE', 1, 0)) AS totalLikes,
        SUM(IF(pe.event = 'SHARE', 1, 0)) AS totalShares,
        SUM(IF(pe.event = 'BOOKMARK', 1, 0)) AS totalBookmarks,
        SUM(IF(pe.event = 'DOWNLOAD', 1, 0)) AS totalDownloads,
        SUM(IF(pe.event = 'FEEDBACK', 1, 0)) AS totalFeedbacks
    FROM postEvents pe  
    JOIN users pu ON pu.id = pe.userId AND pu.deletedOn IS NULL
    GROUP BY pu.id
) p ON p.id = u.id
LEFT JOIN (
    SELECT
        qu.id AS id,
        SUM(IF(qe.event = 'SEEN', 1, 0)) AS totalSeen,
        SUM(IF(qe.event = 'LIKE', 1, 0)) AS totalLikes,
        SUM(IF(qe.event = 'SHARE', 1, 0)) AS totalShares,
        SUM(IF(qe.event = 'BOOKMARK', 1, 0)) AS totalBookmarks
    FROM questionEvents qe  
    JOIN users qu ON qu.id = qe.userId AND qu.deletedOn IS NULL
    GROUP BY qu.id
) q ON q.id = u.id
-- 按用户维度分组，确保每个用户返回一行结果
GROUP BY u.id, u.name;

三、关键调整说明 #

• GROUP BY 子句：主查询必须按用户唯一标识u.id和名称u.name分组，让聚合函数按单个用户计算指标，避免全局合并。
• IFNULL 处理：LEFT JOIN后，无对应事件记录的用户会返回NULL值，用IFNULL(字段, 0)将NULL转为0，保证求和逻辑不出现异常。
• 过滤逻辑：子查询已通过pu.deletedOn IS NULL和qu.deletedOn IS NULL过滤已删除用户，主表的WHERE u.deletedOn IS NULL可根据业务需求选择添加，确保统计范围一致。

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