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 种可能的组合中获得一种。

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

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

Answer 4

只是为了给你一个想法，我有一个带有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;

希望你明白了

Answer 5

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 比较避免重复值/对，最后我们做不同的操作以仅获取不同的对。