如何查找跨列重复值（排除同记录）并展示全部关联记录

需求要点 #

• 筛选存在关联的记录：要么当前记录的STR_ROUTE出现在其他记录的STR_STREET中，要么当前记录的STR_STREET出现在其他记录的STR_ROUTE中
• 排除规则：同一记录内STR_ROUTE与STR_STREET值完全相同的记录
• 结果要求：展示所有符合条件的关联记录（包括匹配双方的记录）

示例数据 #

预期结果 #

原语句问题分析 #

你使用的语句 SELECT * FROM TABLE WHERE STR_ROUTE IN (SELECT STR_STREET FROM TABLE WHERE STR_STREET) 存在两个问题：

1. 未排除同一记录内STR_ROUTE与STR_STREET相等的情况，会误包含ID=3的记录
2. 仅筛选STR_ROUTE存在于STR_STREET的记录，无法覆盖STR_STREET存在于其他记录STR_ROUTE的情况（比如ID=2的记录会被遗漏）

可行解决方案 #

方案：使用EXISTS子查询实现双向关联判断 #

SELECT t.*
FROM your_table t
WHERE (
    -- 当前记录的STR_ROUTE存在于其他记录的STR_STREET中
    EXISTS (SELECT 1 FROM your_table t2 WHERE t2.ID != t.ID AND t.STR_ROUTE = t2.STR_STREET)
    OR
    -- 当前记录的STR_STREET存在于其他记录的STR_ROUTE中
    EXISTS (SELECT 1 FROM your_table t2 WHERE t2.ID != t.ID AND t.STR_STREET = t2.STR_ROUTE)
)
-- 排除同一记录内两列值相同的情况
AND t.STR_ROUTE != t.STR_STREET;

逻辑说明 #

1. 两个EXISTS子查询分别判断记录的双向关联关系，确保不会遗漏任何一方的关联记录
2. t2.ID != t.ID确保匹配的是其他记录，而非当前记录自身
3. 最后通过t.STR_ROUTE != t.STR_STREET排除同一记录内两列值相同的情况

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