如何删除没有唯一行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)
)
当前回答
我喜欢CTE和ROW_NUMBER，因为两者结合起来可以让我们看到哪些行被删除(或更新)，因此只需更改DELETE FROM CTE…SELECT * FROM CTE:
WITH CTE AS(
SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1
DEMO(结果不同;我想这是由于你的打字错误。
COL1 COL2 COL3 COL4 COL5 COL6 COL7
john 1 1 1 1 1 1
sally 2 2 2 2 2 2
由于使用了PARTITION by col1，这个例子通过一个列col1来确定重复项。如果你想包含多个列，只需将它们添加到PARTITION BY:
ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)
其他回答
试着使用:
SELECT linkorder
,Row_Number() OVER (
PARTITION BY linkorder ORDER BY linkorder DESC
) AS RowNum
FROM u_links
如果你没有引用，比如外键，你可以这样做。在测试概念证明和测试数据重复时，我经常这样做。
SELECT DISTINCT [col1],[col2],[col3],[col4],[col5],[col6],[col7]
INTO [newTable]
FROM [oldTable]
进入对象资源管理器并删除旧表。
用旧表的名称重命名新表。
DELETE from search
where id not in (
select min(id) from search
group by url
having count(*)=1
union
SELECT min(id) FROM search
group by url
having count(*) > 1
)
另一种在不丢失信息的情况下删除重复行的方法如下:
delete from dublicated_table t1 (nolock)
join (
select t2.dublicated_field
, min(len(t2.field_kept)) as min_field_kept
from dublicated_table t2 (nolock)
group by t2.dublicated_field having COUNT(*)>1
) t3
on t1.dublicated_field=t3.dublicated_field
and len(t1.field_kept)=t3.min_field_kept
DECLARE @TB TABLE(NAME VARCHAR(100));
INSERT INTO @TB VALUES ('Red'),('Red'),('Green'),('Blue'),('White'),('White')
--**Delete by Rank**
;WITH CTE AS(SELECT NAME,DENSE_RANK() OVER (PARTITION BY NAME ORDER BY NEWID()) ID FROM @TB)
DELETE FROM CTE WHERE ID>1
SELECT NAME FROM @TB;
--**Delete by Row Number**
;WITH CTE AS(SELECT NAME,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) ID FROM @TB)
DELETE FROM CTE WHERE ID>1;
SELECT NAME FROM @TB;
