如何删除没有唯一行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)
)
当前回答
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
其他回答
另一种在不丢失信息的情况下删除重复行的方法如下:
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
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
)
微软有一个关于如何删除重复文件的非常简洁的指南。查看http://support.microsoft.com/kb/139444
简而言之，当你只有几行要删除时，下面是删除重复项的最简单方法:
SET rowcount 1;
DELETE FROM t1 WHERE myprimarykey=1;
Myprimarykey是行标识符。
我将rowcount设置为1，因为我只有两行复制。如果我复制了3行，那么我就会将rowcount设置为2，这样它就会删除它看到的前两行，只在表t1中留下一行。
在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/
-- 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
