如何删除没有唯一行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()，您可以通过使用group by和MAX函数删除记录，这里是一个例子
DELETE
FROM MyDuplicateTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyDuplicateTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
其他回答
您需要根据字段对重复的记录进行分组，然后保留其中一条记录并删除其余记录。 例如:
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)
在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秒)
如果你想删除重复的行并保留最低的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/
我更喜欢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)
删除所有重复项，但删除第一个重复项(具有最小ID)
应该同样适用于其他SQL服务器，如Postgres:
DELETE FROM table
WHERE id NOT IN (
select min(id) from table
group by col1, col2, col3, col4, col5, col6, col7
)
with myCTE
as
(
select productName,ROW_NUMBER() over(PARTITION BY productName order by slno) as Duplicate from productDetails
)
Delete from myCTE where Duplicate>1
