如何查询同一会员下日期范围重叠的记录？SQL语句求助

咱们先梳理下你原来的SQL为啥没生效：

• 没限定同一会员（memid相同），导致跨会员的记录也被匹配了，完全不符合需求
• 没有排除effdate > termdate的无效记录，这些脏数据会干扰结果
• 日期重叠的判断逻辑虽然覆盖了部分情况，但没处理完全包含的场景（比如A记录的时间范围完全包住B记录）

下面给你两个解决方案，兼顾准确性和百万级数据的性能：

基础准确版（适合验证逻辑） #

这个版本用自连接实现，逻辑清晰，适合先验证结果是否符合预期：

SELECT DISTINCT t1.*
FROM zzz_temp t1
JOIN zzz_temp t2 
  ON t1.memid = t2.memid  -- 只在同一会员内匹配
  AND t1.enrid != t2.enrid  -- 排除同一条记录自己匹配自己（enrid是记录唯一ID，没有的话换主键）
  AND t1.effdate <= t1.termdate  -- 过滤t1的无效记录
  AND t2.effdate <= t2.termdate  -- 过滤t2的无效记录
  -- 覆盖所有日期重叠场景：部分重叠、完全包含、完全重合
  AND (
    t1.effdate BETWEEN t2.effdate AND t2.termdate
    OR t1.termdate BETWEEN t2.effdate AND t2.termdate
    OR (t1.effdate <= t2.effdate AND t1.termdate >= t2.termdate)
  );

性能优化版（适合百万级数据） #

百万级数据用自连接会很慢，咱们用窗口函数LAG/LEAD来实现一次扫描就能找出重叠记录，效率提升很多：

WITH valid_records AS (
  -- 先过滤掉所有无效的日期记录
  SELECT *
  FROM zzz_temp
  WHERE effdate <= termdate
),
ranked_records AS (
  SELECT 
    *,
    -- 按会员分组、起始日期排序，获取上一条记录的结束日期
    LAG(termdate) OVER (PARTITION BY memid ORDER BY effdate) AS prev_termdate,
    -- 获取下一条记录的起始日期
    LEAD(effdate) OVER (PARTITION BY memid ORDER BY effdate) AS next_effdate
  FROM valid_records
)
-- 去重后输出结果
SELECT DISTINCT 
  memid, enrid, memfirstname, memlastname, gender, dob, 
  relflag, udfc5, effdate, termdate, lvlid5, lvldesc5, lvlid6, lvldesc6
FROM ranked_records
-- 判断当前记录和上一条/下一条是否重叠
WHERE 
  (effdate <= prev_termdate AND prev_termdate IS NOT NULL)
  OR (termdate >= next_effdate AND next_effdate IS NOT NULL);

额外性能建议 #

为了让查询更快，建议给表加个联合索引：

CREATE INDEX idx_memid_eff_term ON zzz_temp(memid, effdate, termdate);

这个索引不管用哪个版本的查询，都能大幅减少数据库的扫描量。

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