SQL子查询异常：误取全表数据而非指定member_id关联数据

核心问题 #

原查询无返回数据的关键原因：

• 子查询SELECT MIN(b_j_h_s.created_at) FROM b_j_h_s未与主查询关联，取的是全表所有b_j_h_s记录的最小创建时间，而非当前member_id关联的b_j_h_s记录的最小时间，导致日期筛选逻辑完全偏离预期。
• 原WHERE子句中的四个日期条件冗余，四种组合覆盖了所有可能情况，等价于未添加额外筛选（除非业务需排除DATEDIFF等于40的场景）。

修正后的查询语句 #

先通过子查询预计算每个member_id对应的b_j_h_s最小创建时间，再关联到主查询替换原无关联子查询：

SELECT 
    b_j.member_id AS member_id,
    COUNT(b_j_h_s.id) AS h_s_count,
    CAST(IFNULL(SUM(b_j_h_s.status), 0) AS UNSIGNED) AS h_s_status
FROM `b_j`
INNER JOIN `b_j_h_s` ON `b_j`.`id` = `b_j_h_s`.`b_j_id`
INNER JOIN `m_w` ON `b_j`.`member_id` = `m_w`.`member_id`
INNER JOIN `m_j` ON `m_j`.`member_id` = `b_j`.`member_id` AND `m_j`.`j_id` = 4
INNER JOIN `m_w_j` ON `m_w_j`.`m_j_id` = `m_j`.`id` AND `m_w_j`.`m_w_id` = `m_w`.`id`
-- 关联每个member_id对应的最小b_j_h_s创建时间
INNER JOIN (
    SELECT 
        b_j.member_id,
        MIN(b_j_h_s.created_at) AS min_hs_created_at
    FROM b_j
    JOIN b_j_h_s ON b_j.id = b_j_h_s.b_j_id
    GROUP BY b_j.member_id
) AS member_min_hs ON b_j.member_id = member_min_hs.member_id
WHERE 
    `b_j`.`b_s_id` IN (1, 2)
    AND `b_j`.`deleted_at` IS NULL
    -- 保留原日期筛选逻辑，可根据业务需求简化
    AND (
        (DATEDIFF(m_w_j.created_at, b_j.created_at) < 40 AND DATEDIFF(CURDATE(), member_min_hs.min_hs_created_at) > 40)
        OR (DATEDIFF(m_w_j.created_at, b_j.created_at) > 40 AND DATEDIFF(CURDATE(), member_min_hs.min_hs_created_at) > 40)
        OR (DATEDIFF(m_w_j.created_at, b_j.created_at) < 40 AND DATEDIFF(CURDATE(), member_min_hs.min_hs_created_at) < 40)
        OR (DATEDIFF(m_w_j.created_at, b_j.created_at) > 40 AND DATEDIFF(CURDATE(), member_min_hs.min_hs_created_at) < 40)
    )
GROUP BY `member_id`
HAVING h_s_count != h_s_status

额外优化建议 #

• 简化日期条件：原四个日期条件组合后等价于(DATEDIFF(m_w_j.created_at, b_j.created_at) != 40) OR (DATEDIFF(CURDATE(), member_min_hs.min_hs_created_at) != 40)，若业务无需排除等于40的情况，可直接移除这部分条件。
• 兼容无关联记录：若需包含没有b_j_h_s关联记录的member_id，可将INNER JOIN b_j_h_s改为LEFT JOIN b_j_h_s，并在子查询中处理NULL值。

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