此查询返回重复列col、col、col的计数。
SELECT `colB`, `colC`, `colD`, COUNT(1) as CNT
FROM `table`
GROUP BY `colB`, `colC`, `colD`
HAVING COUNT(1) > 1如何在保存记录的同时删除重复的记录?
DELETE FROM `table` WHERE (
// Keep one record and delete rows with duplicate columns b,c,d
)colA是主键- AI
发布于 2015-03-22 16:32:42
如果您有主键,则可以:
delete t
from table t left join
(select cola, colb, colc, min(cola) as cola
from table t
group by cola, colb, colc
) tokeep
on t.cola = tokeep.cola
where tokeep.cola is null;但是,一个表上的许多删除操作可能效率低下。因此,将数据移动到临时表并重新插入它通常更快:
create temporary table tokeep as
select min(cola) as cola, colb, colc, cold
from table t
group by colb, colc, cold
truncate table `table`;
insert into `table`(cola, colb, colc, cold)
select cola, colb, colc, cold
from tokeep;发布于 2015-03-22 16:30:46
尝尝这个
DELETE a
FROM table a
LEFT JOIN
(
SELECT MIN(colA) colA , colB , colC ,colD
FROM Table
GROUP BY colB , colC,colD
) b ON a.colA = b.colA and a.colB = b.colB and a.colC = b.colC and a.colD = b.colD
WHERE b.colA IS NULL发布于 2015-03-22 16:29:25
试一试如下:
DELETE t1
FROM table t1 INNER JOIN table t2
ON t1.colB = t2.ColB
AND t1.colC = t2.ColC
AND t1.colD = t2.ColD
AND t1.colA > t2.ColAhttps://stackoverflow.com/questions/29196813
复制相似问题