如何删除没有唯一行id存在的重复行?

我的座位是

col1  col2 col3 col4 col5 col6 col7
john  1    1    1    1    1    1 
john  1    1    1    1    1    1
sally 2    2    2    2    2    2
sally 2    2    2    2    2    2

我想留下以下重复删除后:

john  1    1    1    1    1    1
sally 2    2    2    2    2    2

我尝试了一些查询,但我认为他们取决于有一个行id,因为我没有得到想要的结果。例如:

DELETE
FROM table
WHERE col1 IN (
    SELECT id
    FROM table
    GROUP BY id
    HAVING (COUNT(col1) > 1)
)

当前回答

DELETE FROM TBL1  WHERE ID  IN
(SELECT ID FROM TBL1  a WHERE ID!=
(select MAX(ID) from TBL1  where DUPVAL=a.DUPVAL 
group by DUPVAL
having count(DUPVAL)>1))

其他回答

请简单地在SELECT命令后添加关键字DISTINCT, 例如:

SELECT DISTICNT ColumnOne, ColumnTwo, ColumnThree
 FROM YourTable

在sql server中可以通过多种方式来实现 最简单的方法是: 将重复行表中的不同行插入到新的临时表中。然后从重复的行表中删除所有数据,然后从没有重复的临时表中插入所有数据,如下所示。

select distinct * into #tmp From table
   delete from table
   insert into table
   select * from #tmp drop table #tmp

   select * from table

使用公共表表达式(CTE)删除重复行

With CTE_Duplicates as 
(select id,name , row_number() 
over(partition by id,name order by id,name ) rownumber  from table  ) 
delete from CTE_Duplicates where rownumber!=1

哦,哇,我觉得准备这些答案太愚蠢了,他们就像专家的答案,包括所有的CTE和临时表等。

为了让它工作,我所做的只是使用MAX聚合ID列。

DELETE FROM table WHERE col1 IN (
    SELECT MAX(id) FROM table GROUP BY id HAVING ( COUNT(col1) > 1 )
)

注意:您可能需要多次运行它来删除重复,因为这一次只会删除一组重复的行。

-- this query will keep only one instance of a duplicate record.
;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2, col3-- based on what? --can be multiple columns
                                       ORDER BY ( SELECT 0)) RN
         FROM   Mytable)



delete  FROM cte
WHERE  RN > 1

如果你没有引用,比如外键,你可以这样做。在测试概念证明和测试数据重复时,我经常这样做。

SELECT DISTINCT [col1],[col2],[col3],[col4],[col5],[col6],[col7]

INTO [newTable]

FROM [oldTable]

进入对象资源管理器并删除旧表。

用旧表的名称重命名新表。