我有一张表,有3列。没有主键,因此可能存在重复的行。我需要保留其中一行并删除其他行。你知道如何在Sql Server中实现吗?
我有一张表,有3列。没有主键,因此可能存在重复的行。我需要保留其中一行并删除其他行。你知道如何在Sql Server中实现吗?
我会选择 DISTINCT 的行并将它们放入一个临时表中,然后删除原始表格并从临时表格中复制回数据。 编辑:现在附上代码片段!
INSERT INTO TABLE_2
SELECT DISTINCT * FROM TABLE_1
GO
DELETE FROM TABLE_1
GO
INSERT INTO TABLE_1
SELECT * FROM TABLE_2
GO
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
set rowcount 1
delete from t1
where col1=1 and col2=1
"rowcount" 的值应该是重复数量的 n-1 倍。在这个例子中有 2 个重复,因此 rowcount 是 1。如果你有多个重复行,则需要针对每个唯一的主键进行此操作。
如果你有许多重复项,则将每个键复制到另一个表中:
SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
然后复制这些键,但是要消除重复项。
SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
在您的密钥中,您现在拥有唯一的密钥。检查是否没有获得任何结果:
SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2
从原始表中删除重复项:
DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
插入原始行:
INSERT t1 SELECT * FROM holddups
顺便提一下,为了完整起见:在Oracle中有一个隐藏字段可以使用(rowid):
DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;
添加一个身份列作为替代主键,并使用它来标识需要删除的三行中的两行。
我建议保留身份列,或者如果这是某种链接表,则在其他列上创建一个复合主键。
这是我在提出这个问题时使用的方法 -
DELETE MyTable
FROM MyTable
LEFT OUTER JOIN (
SELECT MIN(RowId) as RowId, Col1, Col2, Col3
FROM MyTable
GROUP BY Col1, Col2, Col3
) as KeepRows ON
MyTable.RowId = KeepRows.RowId
WHERE
KeepRows.RowId IS NULL
这是一种使用通用表达式(CTE)的方法。它不涉及循环、新列或任何其他操作,也不会触发任何不必要的触发器(由于删除+插入)。
灵感来自于这篇文章。
CREATE TABLE #temp (i INT)
INSERT INTO #temp VALUES (1)
INSERT INTO #temp VALUES (1)
INSERT INTO #temp VALUES (2)
INSERT INTO #temp VALUES (3)
INSERT INTO #temp VALUES (3)
INSERT INTO #temp VALUES (4)
SELECT * FROM #temp
;
WITH [#temp+rowid] AS
(SELECT ROW_NUMBER() OVER (ORDER BY i ASC) AS ROWID, * FROM #temp)
DELETE FROM [#temp+rowid] WHERE rowid IN
(SELECT MIN(rowid) FROM [#temp+rowid] GROUP BY i HAVING COUNT(*) > 1)
SELECT * FROM #temp
DROP TABLE #temp
这是一个棘手的情况。不知道您的具体情况(表大小等),我认为最好的方法是添加一个标识列,填充它,然后根据它删除。您以后可以删除该列,但我建议您保留它,因为在表中使用它确实是一件好事。
select 'set rowcount ' + convert(varchar,COUNT(*)-1) + ' delete from MyTable where field=''' + field +'''' + ' set rowcount 0' from mytable group by field having COUNT(*)>1
然后你只需要执行返回的结果集
set rowcount 3 delete from Mytable where field='foo' set rowcount 0
....
....
set rowcount 5 delete from Mytable where field='bar' set rowcount 0
我已经处理了只有一列的情况,但是将相同的方法适应于多列也很容易。如果您想让我发布代码,请告诉我。
这里有另一种方法,带有测试数据
create table #table1 (colWithDupes1 int, colWithDupes2 int)
insert into #table1
(colWithDupes1, colWithDupes2)
Select 1, 2 union all
Select 1, 2 union all
Select 2, 2 union all
Select 3, 4 union all
Select 3, 4 union all
Select 3, 4 union all
Select 4, 2 union all
Select 4, 2
select * from #table1
set rowcount 1
select 1
while @@rowcount > 0
delete #table1 where 1 < (select count(*) from #table1 a2
where #table1.colWithDupes1 = a2.colWithDupes1
and #table1.colWithDupes2 = a2.colWithDupes2
)
set rowcount 0
select * from #table1
select distinct * into #t from duplicates_tbl
truncate duplicates_tbl
insert duplicates_tbl select * from #t
drop table #t
在清理当前混乱之后,您可以添加一个包含表中所有字段的主键。这将防止您再次陷入混乱。
当然,这个解决方案很可能会破坏现有的代码。这也需要处理。