删除重复的行 [英] delete duplicate rows

问题描述

考虑以下场景，



表1：没有主键

Col1 col2 col3

a 1 0

b 2 0

c 1 0



现在我要删除两行具有相同col2值的行



输出：

Col1 col2 col3

a 1 0

b 2 0





任何人都可以帮我解决这个问题。



谢谢。

consider the following scenario,

Table1:no primary key
Col1 col2 col3
a 1 0
b 2 0
c 1 0

now i want to delete row where two rows having same col2 value

Output:
Col1 col2 col3
a 1 0
b 2 0


Can any one help me to solve this problem.

Thanks.

推荐答案

尝试：

Try:

WITH myCur AS 
   (SELECT ROW_NUMBER() OVER (PARTITION BY Col2 ORDER BY Col1) RowNo
    FROM   MyTable)
DELETE FROM myCur
WHERE RowNo > 1

忘记ORDER BY：doh： - OriginalGriff [/ edit]

[edit]Forgot the ORDER BY :doh: - OriginalGriff[/edit]

试试这个。

Hi,

Try this.

DELETE FROM table1 WHERE col1 NOT IN (SELECT MIN(col1) FROM table1  group by col2 ))

干杯。

Cheers.

这篇关于删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！