如何基于特定组合条件筛选t_TIR表中符合规则的重复记录？

现有两张关联数据表：

• t_TIR：包含strMot、IsVerbe、IsFlexion、ID等字段
• t_DEF：通过ID与t_TIR建立1对多关联，用于存储定义信息

需要筛选出重复的strMot记录（即同一strMot对应多条t_TIR记录），且这些重复记录必须同时包含两类：

1. 关联的t_DEF.strDefinition字段包含"-->"，且对应t_TIR.IsFlexion = FALSE
2. 对应t_TIR.IsVerbe = TRUE且t_TIR.IsFlexion = TRUE

当前使用的SQL语句仅能满足部分条件，无法同时匹配两类记录的要求，现寻求无需拆分查询的高效实现方案。

原尝试SQL语句 #

SELECT t_TIR.strMot, t_TIR.ID, t_Def.strDefinition, t_TIR.IsFlexion, t_TIR.IsVerbe
FROM t_TIR
INNER JOIN t_Def ON t_TIR.ID = t_Def.ID
WHERE (((t_TIR.strMot) IN (
    SELECT [strMot]
    FROM [t_TIR] AS Tmp
    GROUP BY [strMot]
    HAVING Count(*) > 1))
AND ((t_Def.strDefinition) LIKE "*-->*")
AND ((t_TIR.IsFlexion) = False)
AND ((t_TIR.IsVerbe) = False))
ORDER BY t_TIR.strMot;

解决方案SQL #

SELECT t.strMot, t.ID, d.strDefinition, t.IsFlexion, t.IsVerbe
FROM t_TIR t
INNER JOIN t_DEF d ON t.ID = d.ID
WHERE 
    -- 确认当前strMot存在多条记录（重复）
    EXISTS (
        SELECT 1 
        FROM t_TIR t2 
        WHERE t2.strMot = t.strMot 
        GROUP BY t2.strMot 
        HAVING COUNT(*) > 1
    )
    -- 验证同一strMot下存在第一类目标记录
    AND EXISTS (
        SELECT 1 
        FROM t_TIR t3
        INNER JOIN t_DEF d3 ON t3.ID = d3.ID
        WHERE t3.strMot = t.strMot 
          AND d3.strDefinition LIKE "*-->*" 
          AND t3.IsFlexion = FALSE
    )
    -- 验证同一strMot下存在第二类目标记录
    AND EXISTS (
        SELECT 1 
        FROM t_TIR t4
        WHERE t4.strMot = t.strMot 
          AND t4.IsVerbe = TRUE 
          AND t4.IsFlexion = TRUE
    )
ORDER BY t.strMot;

方案说明 #

1. 第一个EXISTS子查询确认当前strMot存在重复记录，满足基础筛选条件
2. 第二个EXISTS子查询验证同一strMot下存在符合第一类条件的记录
3. 第三个EXISTS子查询验证同一strMot下存在符合第二类条件的记录
4. 整体通过关联查询直接拉取符合条件的t_TIR和t_DEF关联数据，无需拆分查询，且EXISTS子查询在性能上优于IN子查询，适合数据量较大的场景

内容的提问来源于stack exchange，提问作者Jean-Michel