MySQL关联查询筛选rdt_date小于dt_date的记录出现重复如何解决

重复原因 #

你遇到的重复是关联逻辑的正常结果：sampleInspect和sampleRec两张表中，同一个idno都存储了多条时间记录，只要两条记录满足i.rdt_date < r.dt_date的关联条件，就会生成一条匹配结果，因此同一个sampleInspect的记录可能对应多条sampleRec的记录，最终出现重复。

适配不同需求的解决方案 #

场景1：每个sampleInspect记录只匹配最近的符合条件的dt_date #

这是最常用的业务场景，仅保留每个检验记录对应的、大于检验时间的最早录入记录，避免一对多匹配产生重复：

SELECT dt_date, idno2, idno, rdt_date
FROM (
    SELECT 
        r.dt_date, 
        r.idno AS idno2, 
        i.idno, 
        i.rdt_date,
        ROW_NUMBER() OVER(PARTITION BY i.idno, i.rdt_date ORDER BY r.dt_date ASC) AS rn
    FROM sampleInspect i
    INNER JOIN sampleRec r 
        ON r.idno = i.idno 
        AND i.rdt_date < r.dt_date
    WHERE i.rdt_date BETWEEN '2021-1-1' AND '2021-11-17'
) t
WHERE rn = 1;

场景2：仅需要去重完全相同的结果行 #

如果你的业务允许保留所有匹配结果，仅需要去除完全重复的行，直接加DISTINCT关键字即可：

SELECT DISTINCT r.dt_date, r.idno as idno2, i.idno, i.rdt_date
FROM  sampleInspect i
INNER JOIN sampleRec r 
    ON r.idno = i.idno 
    AND i.rdt_date < r.dt_date
WHERE i.rdt_date BETWEEN '2021-1-1' AND '2021-11-17'

场景3：仅判断是否存在符合条件的记录，不需要返回dt_date #

如果不需要展示sampleRec的时间字段，用EXISTS查询效率更高，完全不会产生重复：

SELECT i.idno, i.rdt_date
FROM sampleInspect i
WHERE i.rdt_date BETWEEN '2021-1-1' AND '2021-11-17'
AND EXISTS (
    SELECT 1 FROM sampleRec r 
    WHERE r.idno = i.idno AND r.dt_date > i.rdt_date
)

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