从表中删除重复的行

How can I remove duplicate rows from my table? I've searched the Internet, but I haven't been able to solve the issue. Here's what I've written:

如何从表中删除重复的行?我上网查过了，但还没能解决这个问题。这是我写的:

WITH C As
(
Select A.PatientID, A.DoctorID
From Appointment As A
)

Select Distinct A.PatientID, A2.PatientID, A.DoctorID
From Appointment As A
    Inner Join C as A2
        On A.DoctorID = A2.DoctorID
Where A.PatientID <> A2.PatientID
Order By A.PatientID Asc

Here's the outcome:

结果:

In the image above, you'll notice that the data in row 1 is duplicated in row 6. How can I remove all the duplicate rows? Any suggestions?

在上面的图像中，您将注意到第1行中的数据在第6行中是重复的。如何删除所有重复的行?有什么建议吗?

2 个解决方案

#1

You dont need a CTE for this

你不需要CTE

Try

试一试

 SELECT DISTINCT PatientId, PatientId, DoctorID
 FROM Appointment A1
 JOIN Appointment A2
   ON A1.PatientId < A2.PatientId
  AND A1.DoctorID = A2.DoctorID
 Order By A1.PatientID Asc

#2

You can not generate the symmetric dups in the first place by arbitrarily choosing patient A to allways be the one of the pair with the smaller ID

首先，您不能通过任意选择患者A始终是ID较小的一对中的一个来生成对称dup

...
Where A.PatientID < A2.PatientID

This will not help if you have dups in the original table but by its name it should be a primary key and/or have a not NULL & unique index on "PatientID"

如果在原始表中有dup，这不会有帮助，但是根据它的名称，它应该是主键，并且/或在“PatientID”上有一个非NULL & unique索引

#1