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');

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

这样你应该只能从 2 种可能的组合中得到一种。