1:N LEFT JOIN下过滤子表不符合时间条件的主表行最优方案

针对你的需求——剔除所有对应S表存在不符合指定时间范围记录的D表行（即仅保留：无关联S记录的D，或所有关联S记录都满足block_start >= :start AND block_end <= :end的D），以下是几种高效的实现方案，比你原有的分组聚合思路性能更优：

方案1：使用NOT EXISTS排除不符合条件的D行 #

这是最常用且性能最优的方案之一，数据库优化器通常能很好地处理这种逻辑，配合合适的索引可以快速定位不符合条件的记录：

SELECT d.*, s.*
FROM d
LEFT JOIN s ON s.d_id = d.id
WHERE coalesce(:status, d.status) = d.status
  AND NOT EXISTS (
    -- 找到当前D下存在任何不符合时间范围的S记录
    SELECT 1
    FROM s s_invalid
    WHERE s_invalid.d_id = d.id
      AND (s_invalid.block_start < :start OR s_invalid.block_end > :end)
  )

逻辑说明 #

如果某个D对应的S表中存在任意一条不满足时间范围的记录，NOT EXISTS就会返回false，该D行就会被过滤掉；反之则保留（包括无关联S的D行）。

方案2：使用LEFT JOIN + IS NULL实现等价逻辑 #

和方案1逻辑完全一致，只是写法不同，适合习惯用JOIN写法的场景：

SELECT d.*, s.*
FROM d
LEFT JOIN s ON s.d_id = d.id
-- 左连所有不符合时间范围的S记录
LEFT JOIN s s_invalid ON s_invalid.d_id = d.id
  AND (s_invalid.block_start < :start OR s_invalid.block_end > :end)
WHERE coalesce(:status, d.status) = d.status
  -- 筛选出没有匹配到不符合条件S记录的D行
  AND s_invalid.id IS NULL

方案3：优化后的分组聚合（仅适合需额外聚合场景） #

如果你原有的分组思路是因为需要同时获取min/max时间，那么可以优化HAVING条件，避免不必要的聚合计算：

SELECT d.*, s.*
FROM d
LEFT JOIN s ON s.d_id = d.id
WHERE coalesce(:status, d.status) = d.status
  AND d.id IN (
    -- 筛选所有关联S都符合时间范围的D
    SELECT d_id
    FROM s
    GROUP BY d_id
    HAVING SUM(CASE WHEN block_start >= :start AND block_end <= :end THEN 0 ELSE 1 END) = 0
    -- 补充无关联S的D行
    UNION ALL
    SELECT id
    FROM d
    WHERE NOT EXISTS (SELECT 1 FROM s WHERE s.d_id = d.id)
  )

索引优化建议 #

无论选择哪种方案，都建议给S表创建联合索引：

CREATE INDEX idx_s_did_block ON s (d_id, block_start, block_end);

这个索引可以让数据库快速定位指定d_id下的时间范围记录，避免全表扫描，大幅提升查询效率。

方案对比 #

• 方案1/2：性能最优，执行计划通常会采用"半连接"或"反连接"逻辑，仅扫描必要的数据，适合绝大多数场景。
• 方案3：仅当你需要同时获取D对应的S时间聚合值（如min/max）时使用，单纯过滤的话性能不如前两种。

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