SQL - 如何避免自连接/内连接中的重复对？

Question

我有一个数据库，其中除其他表之外，还有这个表：

CREATE TABLE `Physiotherapist`(
`pid` INT,
`name` VARCHAR(40),
`hours` INT,
`cid` INT    

我想编写一个SQL查询，返回在同一诊所工作的物理治疗师对列表，但不会返回重复结果。到目前为止，我的答案是：

SELECT  p1.name AS name1, p2.name AS name2
FROM    Physiotherapist p1 
INNER JOIN Physiotherapist p2 on p2.cid = p1.cid AND p1.name != p2.name

或者通过自连接实现替代解决方案：

SELECT  p1.name, p2.name
FROM    Physiotherapist p1, Physiotherapist p2
WHERE   p1.cid = p2.cid AND p1.name !=(SELECT p2.name)

无论哪种方式，我都能得到这个结果：

name1:            name2:

Jan Christensen   Ira Assent
Ira Assent        Jan Christensen

即，同一对数据。我只需要其中的一个副本，因为它们显示相同的信息，但我无法找到一种方便的方法来实现。

编辑：带有示例数据的完整数据库代码

DROP TABLE IF EXISTS Physiotherapist;

CREATE TABLE `Physiotherapist`(
    `pid` INT,
    `name` VARCHAR(40),
    `hours` INT,
    `cid` INT
);

INSERT INTO Physiotherapist VALUES('123','Ira Assent','8','1');
INSERT INTO Physiotherapist VALUES('246','Annika Schmidt','1','2');
INSERT INTO Physiotherapist VALUES('327','Jan Christensen','5','1');
INSERT INTO Physiotherapist(`pid`,`name`) VALUES('455','Simon Winter');

DROP TABLE IF EXISTS Clinic;

CREATE TABLE `Clinic`(
    `cid` INT,
    `name` VARCHAR(40),
    `address` VARCHAR(40)
);

INSERT INTO Clinic VALUES ('1','PhysCentral','Aarhus C');
INSERT INTO Clinic VALUES ('2','PhysIOgnomy','Aarhus N');
INSERT INTO Clinic VALUES ('3','Physios','Aarhus V');

DROP TABLE IF EXISTS Appointment;

CREATE TABLE `Appointment`(
    `aid` INT,
    `date` DATE,
    `time` INT,
    `pid` INT
);

INSERT INTO Appointment VALUES(34716,'2018-06-12','10','246');
INSERT INTO Appointment VALUES(23118,'2018-08-18','11','327');
INSERT INTO Appointment VALUES(88223,'2018-10-03','9','246');

- hostergaard

---

请添加更多的示例表数据和新的预期结果。 - jarlh

示例数据和预期输出有助于指导请将以下与程序有关的内容从英语翻译为中文。仅返回已翻译的文本： - Zaynul Abadin Tuhin

我已添加了包含所有示例数据的完整表格。 - hostergaard

要编写一个SQL查询，返回在同一诊所工作的物理治疗师配对列表，但不会返回重复项。配对可以是(Jan Christensen - Ira Assent)或(Ira Assent - Jan Christensen)，但不能同时出现。 - hostergaard

嗨。这是一个常见问题解答。请始终使用谷歌搜索您的问题/目标的许多清晰、简明和具体的版本/措辞，包括和不包括您特定的字符串/名称，并阅读许多答案。将您发现的相关关键字添加到您的搜索中。如果您找不到答案，则发布帖子，使用一个变体搜索您的标题和关键字作为您的标签。请参阅向下箭头悬停文本。 - philipxy

Answer 1

尝试

SELECT  p1.name, p2.name
FROM    Physiotherapist p1, Physiotherapist p2
WHERE   p1.cid = p2.cid AND p1.name !=(SELECT p2.name) AND p1.pid > p2.pid

这样你只能从可能的两个组合中得到一个。

- LostReality

---

它生成了（Jan Christensen - Ira Assent）作为结果。虽然它确实产生了我想要的，但我想知道它是否是正确的答案。非常感谢！ - hostergaard

添加更多的治疗师并不能解决问题，但还是谢谢。 - hostergaard

Answer 2

只需将pid <> pid（假设它是主键，从您的定义中看起来确实是如此）也包含在内;

SELECT  p1.name AS name1, p2.name AS name2
FROM    Physiotherapist p1 
INNER JOIN Physiotherapist p2 
    on p2.cid = p1.cid 
    AND p1.name != p2.name 
    -- Add this to WHERE
    AND p1.pid != p2.pid

- Milney

---

它似乎仍然在相反的列中产生重复的对。虽然我显然不需要检查等效名称。我很感激你的回答，谢谢。 - hostergaard

Answer 3

我认为下面的查询语句适合你

select T3.* from 
(select clname,count( distinct pname) from
(SELECT  p1.name as pname,c.name as clname
FROM    Physiotherapist p1 inner join
 Clinic c on p1.cid=c.cid
 ) as T  GROUP BY clname
 having count( distinct pname)>1
) T2 inner join        

 (SELECT  p1.name as pname,c.name as clname
FROM    Physiotherapist p1 inner join
 Clinic c on p1.cid=c.cid
  ) t3 on t2.clname=t3.clname

http://sqlfiddle.com/#!9/1cbace/10

- Zaynul Abadin Tuhin

---

嗯，虽然这似乎即使我添加更多的治疗师也不会产生重复，但我正在处理的问题要求我“编写一个SQL查询，返回在同一诊所工作的物理治疗师对列表。不要返回重复项。”也就是说，我需要将它们成对出现。但是非常出色的工作，非常感谢！ - hostergaard

Answer 4

SELECT  DISTINCT p1.name, p2.name
FROM    Physiotherapist p1 INNER JOIN Physiotherapist p2
ON  p1.cid = p2.cid AND p1.pid>p2.pid

在这里，我们使用诊所ID进行自连接以获取成对数据，where条件中的pid比较避免了重复值/成对数据，最后我们使用distinct来获取唯一的成对数据。

- Sidharthan PV

Answer 5

仅仅为了给你一个概念，我有一个包含authorid和bookid字段的出版物表。要获取至少在一本书中合作过的作者，我的SQL查询是：

SELECT p1.authorid , p2.authorid
FROM publications p1 , publications p2
WHERE p1.bookid = p2.bookid AND p1.authorid<>p2.authorid AND p1.authorid>p2.authorid;

希望你明白了

- Sihat Afnan