高效SQL实现四列表任意三列值相等数据筛选

你的写法存在两个核心问题，是导致结果错误、查询极慢的根本原因：

• 逻辑优先级错误：SQL中AND运算优先级高于OR，你写的t.mud_id=285仅对最后一个三字段匹配条件生效，等价于：

  WHERE 条件1 OR 条件2 OR 条件3 OR (条件4 AND t.mud_id=285)
  

  前三个匹配分支完全没有mud_id过滤，会扫描全表所有符合三字段重复的数据，完全不符合你的查询预期。
• 性能极差：4个IN子查询均为全表分组聚合，没有提前缩小数据范围，大表场景下会产生多次全表扫描、磁盘临时表，查询耗时会指数级上升。

以下写法支持MySQL 8.0+、PostgreSQL等支持CTE的数据库，核心逻辑是先过滤目标mud_id范围的数据集（把计算量压缩到最小），再匹配存在三字段重复的行：

WITH target_data AS (
    -- 第一步就过滤目标范围数据，避免全表无效计算
    SELECT id, `right`, `left`, `up`, `down`
    FROM test_table
    WHERE mud_id = 285
)
SELECT DISTINCT t.*
FROM target_data t
-- 匹配任意三个字段跨行列完全相等的记录
WHERE EXISTS (
    SELECT 1
    FROM target_data t2
    WHERE t2.id <> t.id
    AND (
        (t2.`right` = t.`right` AND t2.`left` = t.`left` AND t2.`up` = t.`up`)
        OR (t2.`right` = t.`right` AND t2.`left` = t.`left` AND t2.`down` = t.`down`)
        OR (t2.`right` = t.`right` AND t2.`up` = t.`up` AND t2.`down` = t.`down`)
        OR (t2.`left` = t.`left` AND t2.`up` = t.`up` AND t2.`down` = t.`down`)
    )
)
ORDER BY t.`right`, t.`left`, t.`up`, t.`down`;

如果使用不支持CTE的低版本MySQL（5.x），可以改用兼容写法：

SELECT DISTINCT t.*
FROM test_table t
WHERE t.mud_id = 285
AND EXISTS (
    SELECT 1
    FROM test_table t2
    WHERE t2.mud_id = 285
    AND t2.id <> t.id
    AND (
        (t2.`right` = t.`right` AND t2.`left` = t.`left` AND t2.`up` = t.`up`)
        OR (t2.`right` = t.`right` AND t2.`left` = t.`left` AND t2.`down` = t.`down`)
        OR (t2.`right` = t.`right` AND t2.`up` = t.`up` AND t2.`down` = t.`down`)
        OR (t2.`left` = t.`left` AND t2.`up` = t.`up` AND t2.`down` = t.`down`)
    )
)
ORDER BY t.`right`, t.`left`, t.`up`, t.`down`;

如果表数据量较大，可以创建覆盖索引，让查询直接走索引完成，不需要回表读取数据：

CREATE INDEX idx_mud_fields ON test_table(mud_id, `right`, `left`, `up`, `down`);

加索引后，上述查询的耗时可以从分钟级降到毫秒级。

针对你给出的样例数据，上述写法会正确返回id=1、id=2的两行；id=3、4、5均不存在同mud_id下三个字段完全匹配的其他行，不会被返回，完全符合预期。

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