unique row 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)
)
#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
COL1 COL2 COL3 COL4 COL5 COL6 COL7
john 1 1 1 1 1 1
sally 2 2 2 2 2 2
由于
col1
,此示例通过单列PARTITION BY col1
确定重复项。如果要包括多个列,只需将它们添加到PARTITION BY
中:ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)
评论
@ omachu23:在这种情况下,这没关系,尽管我认为CTE的效率要比外界的效率高(AND COl1 ='John')。通常,您应该在CTE中应用过滤器。
– Tim Schmelter
2015年2月11日在21:18
@ omachu23:您可以在CTE中使用任何SQL(除订购外),因此如果要按Johns进行过滤:... FROM dbo.Table1 WHERE Col1 ='John'。这是小提琴:sqlfiddle.com/#!6/fae73/744/0
– Tim Schmelter
2015年2月11日在22:23
最简单的解决方案可能只是将rowcount 1从t1删除,其中col1 = 1和col2 = 1,如此处所示
–佐加拉特
2015年4月29日在16:23
此答案只会删除col1中重复的行。将“选择”中的列添加到“分区依据”中,例如使用答案中的选择:RN = ROW_NUMBER()OVER(PARTITION BY col1,col2,col3,col4,col5,col6,col7 ORDER BY col1)
– rlee
16 Mar 16 '16 at 11:26
CTE是什么意思,当我输入时会出现sql错误。
–白猫
16年8月5日在22:10
#2 楼
我希望CTE从sql server表中删除重复的行强烈建议您遵循本文:: http://codaffection.com/sql-server-article/delete-duplicate-rows-in- sql-server /
通过保留原始
WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)
DELETE FROM CTE WHERE RN<>1
而无需保留原始
WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)
评论
窗口功能是一个很好的解决方案。
–罗伯特·凯西(Robert Casey)
16年8月25日在16:08
我有点困惑。您从CTE而不是原始表中删除了它。那么它是怎样工作的?
–大眼
19年4月19日在20:29
@Bigeyes从CTE删除记录将从实际物理表中删除相应的记录。(因为CTE包含对实际记录的引用)。
– Shamseer K
19年4月20日在11:26
在发布这篇文章之前,我不知道是这种情况。谢谢
–扎克·迪亚兹(Zakk Diaz)
19年8月19日在22:41
为什么要同时删除原始副本和副本副本?我不明白为什么您不希望只删除重复项并保留另一个。
–丰富
19年8月26日在3:23
#3 楼
无需使用CTE
和ROW_NUMBER()
,您可以仅通过使用MAX
函数的group by来删除记录,这是example和评论
该查询将删除非重复记录。
–Derek Smalls
17年11月30日在16:01
效果很好,谢谢。 @DerekSmalls,这不会删除我的非重复记录。
–monteirobrena
17年12月7日13:08
或者您可以使用MIN(ID)保留原始记录
–野蛮人
19年11月21日在20:08
#4 楼
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
)
评论
您是否可以重写为:id in(选择max(id)...具有count(*)> 1)?
–布伦特
16年2月10日在16:01
我不认为有必要使用had或union,这就足够了:从搜索中删除ID不在的地方(从搜索组中按URL选择min(id))
–杨Chris
16 Mar 7 '16 at 20:14
#5 楼
如果没有引用(例如外键),则可以执行此操作。在测试概念验证和测试数据重复时,我会做很多事情。SELECT DISTINCT [col1],[col2],[col3],[col4],[col5],[col6],[col7]
INTO [newTable]
FROM [oldTable]
进入对象资源管理器并删除旧表。
用旧表的名称重命名新表。
评论
这是我在介绍资料中学习并使用的最简单的方法。
–eric
19/12/14在3:10
奇怪的是,当[oldTable]拥有数十亿行时,此答案的效果如何?
–牛
12月19日4:37
#6 楼
也请参见下面的删除方法。Declare @table table
(col1 varchar(10),col2 int,col3 int, col4 int, col5 int, col6 int, col7 int)
Insert into @table values
('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)
创建了一个名为
@table
的示例表并加载了给定的数据。Delete aliasName from (
Select *,
ROW_NUMBER() over (Partition by col1,col2,col3,col4,col5,col6,col7 order by col1) as rowNumber
From @table) aliasName
Where rowNumber > 1
Select * from @table
注意:如果要在
Partition by
部分中提供所有列,则order by
的意义不大。我知道,这个问题是三年前提出的,而我的回答是蒂姆(Tim)发布的内容的另一个版本,但是发布只是为了以防万一。
评论
这个更可靠
–罗兹比·扎兰迪(Rouzbeh Zarandi)
11月7日15:42
#7 楼
Microsoft有一个关于如何删除重复项的简洁指南。请查看http://support.microsoft.com/kb/139444总之,这是删除几行时最简单的删除重复项的方法:
SET rowcount 1;
DELETE FROM t1 WHERE myprimarykey=1;
myprimarykey是该行的标识符。
我将rowcount设置为1,因为只有两行重复了。如果我重复了3行,那么我将把rowcount设置为2,这样它将删除它看到的前两行,而只在表t1中保留一行。
希望对任何人都可以帮助
评论
如果我有1万行,我怎么知道我重复了多少行?
– Fearghal
2014年6月6日9:20
@Fearghal尝试“通过primaryKey从myTable组中选择primaryKey,count(*);”
–oabarca
2014年6月7日15:15
但是,如果重复行的数量不同,该怎么办?即a行有2条记录,b行有5条记录,c行没有重复的记录
–thermite
2014年11月4日在16:16
@ user2070775如果所有行中只有一个子集具有重复项,而在那些重复项中,有一些重复了两次,而有些则被重复了三四次呢?
–thermite
2014年11月4日在17:20
@ user2070775我错过了您说“只需删除几行”的部分。页面上也有关于设置行数的警告,在将来的sql版本中,它不会影响更新或删除语句
–thermite
2014年11月4日17:27
#8 楼
mysql
中有两种解决方案:A)使用
DELETE JOIN
语句删除重复的行DELETE t1 FROM contacts t1
INNER JOIN contacts t2
WHERE
t1.id < t2.id AND
t1.email = t2.email;
此查询两次引用联系人表,因此,它使用表别名
t1
和t2
。输出为:
1
查询正常,受影响的4行(0.10秒)
如果要删除重复的行并保留
lowest id
,可以使用以下语句:DELETE c1 FROM contacts c1
INNER JOIN contacts c2
WHERE
c1.id > c2.id AND
c1.email = c2.email;
B)使用中间表删除重复的行
以下显示了使用中间表删除重复的行的步骤:
1。创建具有与要删除重复行的原始表相同的结构的新表。
2.将原始表中的不同行插入到直接表中。
3.从原始表到直接表插入不同的行。
步骤1。创建一个结构与原始表相同的新表:
CREATE TABLE source_copy LIKE source;
步骤2。将原始表的不同行插入到新表中:
INSERT INTO source_copy
SELECT * FROM source
GROUP BY col; -- column that has duplicate values
步骤3.删除原始表,并将立即表重命名为原始表。
DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;
来源:http: //www.mysqltutorial.org/mysql-delete-duplicate-rows/
评论
我的表中大约有190000行。对于这么多的行,sol 1不是一个好的选择。 sol 2对我来说效果很好。谢谢
– Nirav Chavda
11月2日,11:42
#9 楼
尝试使用:SELECT linkorder
,Row_Number() OVER (
PARTITION BY linkorder ORDER BY linkorder DESC
) AS RowNum
FROM u_links
#10 楼
可以通过sql server中的许多方法来实现。最简单的方法是:将重复行表中的不同行插入到新的临时表中。然后从重复行表中删除所有数据,然后从临时表中插入没有重复项的所有数据,如下所示。
)
select distinct * into #tmp From table
delete from table
insert into table
select * from #tmp drop table #tmp
select * from table
#11 楼
在尝试了上述建议的解决方案之后,该方法适用于中小型表。我可以建议大型表的解决方案。因为它是在迭代中运行。
删除
LargeSourceTable
的所有依赖关系视图您可以使用sql managment studio查找依赖项,右键单击表格,然后单击“查看依赖项”
重命名表:
sp_rename 'LargeSourceTable', 'LargeSourceTable_Temp'; GO
再次创建
LargeSourceTable
,但是现在,添加一个具有定义重复项的所有列的主键,并添加WITH (IGNORE_DUP_KEY = ON)
例如:
CREATE TABLE [dbo].[LargeSourceTable]
(
ID int IDENTITY(1,1),
[CreateDate] DATETIME CONSTRAINT [DF_LargeSourceTable_CreateDate] DEFAULT (getdate()) NOT NULL,
[Column1] CHAR (36) NOT NULL,
[Column2] NVARCHAR (100) NOT NULL,
[Column3] CHAR (36) NOT NULL,
PRIMARY KEY (Column1, Column2) WITH (IGNORE_DUP_KEY = ON)
);
GO
再次为新创建的表创建放置在第一位的视图
现在,运行在以下sql脚本中,您将看到每页1,000,000行的结果,您可以更改每页的行号以更频繁地查看结果。
请注意,我将
IDENTITY_INSERT
设置为开和关,因为其中一列包含自动增量ID,我也在复制SET IDENTITY_INSERT LargeSourceTable ON
DECLARE @PageNumber AS INT, @RowspPage AS INT
DECLARE @TotalRows AS INT
declare @dt varchar(19)
SET @PageNumber = 0
SET @RowspPage = 1000000
select @TotalRows = count (*) from LargeSourceTable_TEMP
While ((@PageNumber - 1) * @RowspPage < @TotalRows )
Begin
begin transaction tran_inner
; with cte as
(
SELECT * FROM LargeSourceTable_TEMP ORDER BY ID
OFFSET ((@PageNumber) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY
)
INSERT INTO LargeSourceTable
(
ID
,[CreateDate]
,[Column1]
,[Column2]
,[Column3]
)
select
ID
,[CreateDate]
,[Column1]
,[Column2]
,[Column3]
from cte
commit transaction tran_inner
PRINT 'Page: ' + convert(varchar(10), @PageNumber)
PRINT 'Transfered: ' + convert(varchar(20), @PageNumber * @RowspPage)
PRINT 'Of: ' + convert(varchar(20), @TotalRows)
SELECT @dt = convert(varchar(19), getdate(), 121)
RAISERROR('Inserted on: %s', 0, 1, @dt) WITH NOWAIT
SET @PageNumber = @PageNumber + 1
End
SET IDENTITY_INSERT LargeSourceTable OFF
#12 楼
-- 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
#13 楼
您需要根据字段将重复记录分组,然后保留其中一条记录并删除其余记录。例如:
DELETE prg.Person WHERE Id IN (
SELECT dublicateRow.Id FROM
(
select MIN(Id) MinId, NationalCode
from prg.Person group by NationalCode having count(NationalCode ) > 1
) GroupSelect
JOIN prg.Person dublicateRow ON dublicateRow.NationalCode = GroupSelect.NationalCode
WHERE dublicateRow.Id <> GroupSelect.MinId)
#14 楼
从巨大的(几百万个记录)表中删除重复项可能需要很长时间。我建议您对所选行的临时表进行批量插入,而不要删除。--REWRITING YOUR CODE(TAKE NOTE OF THE 3RD LINE) WITH CTE AS(SELECT NAME,ROW_NUMBER()
OVER (PARTITION BY NAME ORDER BY NAME) ID FROM @TB) SELECT * INTO #unique_records FROM
CTE WHERE ID =1;
#15 楼
这可能对您有帮助DELETE t1 FROM table t1 INNER JOIN table t2 WHERE t1.id > t2.id AND t1.col1 = t2.col1
#16 楼
with myCTE
as
(
select productName,ROW_NUMBER() over(PARTITION BY productName order by slno) as Duplicate from productDetails
)
Delete from myCTE where Duplicate>1
#17 楼
参考https://support.microsoft.com/en-us/help/139444/how-to-remove-dumove-rows-from-a-table-in-sql-server删除重复项的想法涉及
a)保护那些不重复的行
b)保留许多一起被视为重复的行之一。 br循序渐进
1)首先确定满足重复的定义的行
,并将其插入到临时表中,例如#tableAll。
2)在临时表中选择非重复(单行)或不同的行
说#tableUnique。
3)从源表中删除,并加入#tableAll以删除
重复项。
4)将#tableUnique中的所有行插入源表。
5)删除#tableAll和#tableUnique
#18 楼
如果您可以临时向表中添加列,那么这对我来说是一种解决方案:ALTER TABLE dbo.DUPPEDTABLE ADD RowID INT NOT NULL IDENTITY(1,1)
然后使用MIN和组合执行DELETE GROUP BY
DELETE b
FROM dbo.DUPPEDTABLE b
WHERE b.RowID NOT IN (
SELECT MIN(RowID) AS RowID
FROM dbo.DUPPEDTABLE a WITH (NOLOCK)
GROUP BY a.ITEM_NUMBER,
a.CHARACTERISTIC,
a.INTVALUE,
a.FLOATVALUE,
a.STRINGVALUE
);
验证DELETE是否正确执行:
SELECT a.ITEM_NUMBER,
a.CHARACTERISTIC,
a.INTVALUE,
a.FLOATVALUE,
a.STRINGVALUE, COUNT(*)--MIN(RowID) AS RowID
FROM dbo.DUPPEDTABLE a WITH (NOLOCK)
GROUP BY a.ITEM_NUMBER,
a.CHARACTERISTIC,
a.INTVALUE,
a.FLOATVALUE,
a.STRINGVALUE
ORDER BY COUNT(*) DESC
结果中不应包含任何行大于1的计数。最后,删除rowid列:
ALTER TABLE dbo.DUPPEDTABLE DROP COLUMN RowID;
#19 楼
哦,哇,准备好所有这些答案让我感到非常愚蠢,它们就像专家对所有CTE和temp表等的回答一样。我要做的就是简单地汇总ID列通过使用MAX。
DELETE FROM table WHERE col1 IN (
SELECT MAX(id) FROM table GROUP BY id HAVING ( COUNT(col1) > 1 )
)
注意:您可能需要多次运行以删除重复项,因为这一次只会删除一组重复行。 />
评论
这将不起作用,因为它将删除所有重复项而不保留原始文件。 OP要求保留原始记录。
– 0xdd
18年7月17日在12:58
不正确,max会给您满足条件的max ID。如果事实并非如此,请证明您的不赞成票。
–陷入困境
18年7月17日在13:08
#20 楼
另一种在不丢失信息的情况下一步删除重复行的方法如下: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
#21 楼
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;
评论
从巨大的(几百万个记录)表中删除重复项可能需要很长时间。我建议您对选定行的临时表进行批量插入,而不要删除。 '-使用CTE AS(选择名称,ROW_NUMBER()覆盖(按名称顺序按名称排列)ID从@TB重写您的代码(注意3RD线)*从CTE ID = = 1的#unique_records中写入'
–伊曼纽尔·布尔(Emmanuel Bull)
19年8月15日在10:42
#22 楼
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))
#23 楼
应该与其他SQL服务器(如Postgres)一样工作:DELETE FROM table
WHERE id NOT IN (
select min(id) from table
group by col1, col2, col3, col4, col5, col6, col7
)
评论
这不是第一个链接的重复。该问题中没有行ID,链接的问题中有行ID。完全不同。将'SELECT ID FROM table GROUP BY id HAVING'更改为具有聚合函数,例如MAX / MIN,它应该可以工作。