如何删除具有两列的表中的重复行

Question

我有下表：

索赔#1		索赔#2
ABC	123
123	ABC
DEF	456
789	GHI

您可以在这里使用最小/最大的技巧：

SELECT DISTINCT
CASE WHEN Claim1 < Claim2 THEN Claim1 ELSE Claim2 END AS Claim1,
CASE WHEN Claim1 < Claim2 THEN Claim2 ELSE Claim1 END AS Claim2
FROM yourTable;

如果您的SQL数据库恰好支持标量LEAST()和GREATEST()函数，那么我们可以简化为：

SELECT DISTINCT
LEAST(Claim1, Claim2) AS Claim1,
GREATEST(Claim1, Claim2) AS Claim2
FROM yourTable;

• 选择所有不存在的(claim1，claim2(值(c2.id is null(，如(claim2，claim1(
• 并且从具有反向匹配(not c2.id is null(的记录中仅选择claim1<索赔2

select c1.*
from claims c1
left join claims c2 on c2.claim1=c1.claim2 and c2.claim2=c1.claim1
where c2.id is null
or c1.claim1<c1.claim2 and not c2.id is null

DBFIDDLE

我相信有一些聪明的方法可以做到这一点，但我只是用蛮力的方法。当然没有优化，但易于理解并根据需要进行调整。

首先，我们需要对其自身进行内部联接，并获取要删除的值，然后循环它并一次删除一个值。

IF OBJECT_ID('claims') IS NOT NULL
DROP TABLE claims
GO
CREATE TABLE [dbo].[claims](
[id] [int] IDENTITY(1,1) NOT NULL,
[claim1] [varchar](50) NULL,
[claim2] [varchar](50) NULL,
CONSTRAINT [PK_claims] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO claims VALUES 
('ABC', '123'), 
('123', 'ABC'),
('DEF', '456'),
('456', '555'),
('789', 'GHI'),
('111', 'AAA'),
('AAA', '111')
GO
DECLARE @CONTINUE INT = 1
WHILE @CONTINUE <> 0
BEGIN
DELETE TOP(1) FROM CLAIMS WHERE id IN (
SELECT a.id FROM CLAIMS a
JOIN CLAIMS b
ON a.claim1 = b.claim2
AND b.claim2 = a.claim1
)
SET @CONTINUE = @@ROWCOUNT
END
GO
SELECT * FROM claims