如何编写SQL查询单表中myrefid匹配其他行refid的用户记录

示例基础表 #

首先给出测试用的表结构与初始化数据（注：原示例插入语句存在语法错误，已修正行尾误写的分号为逗号，可直接执行）：

CREATE TABLE IF NOT EXISTS `docs` 
(
    `refid` int(6) unsigned NOT NULL,
    `myrefid` int(3) unsigned NOT NULL,
    `user` varchar(200) NOT NULL,
    PRIMARY KEY (`refid`,`myrefid`)
) DEFAULT CHARSET=utf8;

INSERT INTO `docs` (`refid`, `myrefid`, `user`) 
VALUES ('1', '5', 'user1'),
       ('2', '1', 'user2'),
       ('9', '3', 'user3'),
       ('8', '3', 'user4'),
       ('4', '3', 'user5'),
       ('4', '8', 'user6');

筛选规则 #

需要返回满足以下条件的记录：

• 当前行的myrefid字段值，与同表内其他任意行的refid字段值相等
• 基于上述测试数据，预期返回user2、user6两条记录：
  • user2行的myrefid=1，匹配user1行的refid=1
  • user6行的myrefid=8，匹配user4行的refid=8

查询实现 #

写法1：EXISTS子查询（推荐） #

语义清晰，无需额外去重，执行效率高：

SELECT d1.*
FROM docs d1
WHERE EXISTS (
    SELECT 1
    FROM docs d2
    WHERE d2.refid = d1.myrefid
);

写法2：自连接 #

通过表自身关联实现匹配，注意需要加DISTINCT避免同一条记录匹配多行时出现重复结果：

SELECT DISTINCT d1.*
FROM docs d1
INNER JOIN docs d2 ON d1.myrefid = d2.refid;

执行结果 #

两种写法执行后返回结果一致，如下：

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