問題描述

我在一個表中有多組重復項(一個表有 3 條記錄，另一個表有 2 條記錄，等等)- 存在多于 1 條記錄的多行.

I have multiple groups of duplicates in one table (3 records for one, 2 for another, etc) - multiple rows where more than 1 exists.

以下是我想刪除它們的方法，但是無論有多少重復項，我都必須運行腳本:

Below is what I came up with to delete them, but I have to run the script for however many duplicates there are:

set rowcount 1
delete from Table
where code in (
  select code from Table 
  group by code
  having (count(code) > 1)
)
set rowcount 0

這在一定程度上效果很好.我需要為每組重復項運行它，然后它只刪除 1 個(這是我現在需要的全部).

This works well to a degree. I need to run this for every group of duplicates, and then it only deletes 1 (which is all I need right now).

推薦答案

如果您在表中有一個鍵列，那么您可以使用它來唯一標識表中的不同"行.

If you have a key column on the table, then you can use this to uniquely identify the "distinct" rows in your table.

只需使用子查詢來識別唯一行的 ID 列表，然后刪除該集合之外的所有內容.類似......的東西

Just use a sub query to identify a list of ID's for unique rows and then delete everything outside of this set. Something along the lines of.....

create table #TempTable
(
    ID int identity(1,1) not null primary key,
    SomeData varchar(100) not null
)

insert into #TempTable(SomeData) values('someData1')
insert into #TempTable(SomeData) values('someData1')
insert into #TempTable(SomeData) values('someData2')
insert into #TempTable(SomeData) values('someData2')
insert into #TempTable(SomeData) values('someData2')
insert into #TempTable(SomeData) values('someData3')
insert into #TempTable(SomeData) values('someData4')

select * from #TempTable

--Records to be deleted
SELECT ID
FROM #TempTable
WHERE ID NOT IN
(
    select MAX(ID)
    from #TempTable
    group by SomeData
)

--Delete them
DELETE
FROM #TempTable
WHERE ID NOT IN
(
    select MAX(ID)
    from #TempTable
    group by SomeData
)

--Final Result Set
select * from #TempTable

drop table #TempTable;

或者，您可以使用 CTE，例如:

Alternatively you could use a CTE for example:

WITH UniqueRecords AS
(
    select MAX(ID) AS ID
    from #TempTable
    group by SomeData
)
DELETE A
FROM #TempTable A
    LEFT outer join UniqueRecords B on
        A.ID = B.ID
WHERE B.ID IS NULL

這篇關于刪除表中的多個重復行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！