首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Mysql -删除列重复的行

问Mysql -删除列重复的行
EN

Stack Overflow用户
提问于 2015-03-22 16:23:28
回答 3查看 80关注 0票数 0

此查询返回重复列col、col、col的计数。

代码语言:javascript
复制
SELECT `colB`, `colC`, `colD`, COUNT(1) as CNT
FROM `table`
GROUP BY `colB`, `colC`, `colD` 
HAVING COUNT(1) > 1

如何在保存记录的同时删除重复的记录?

代码语言:javascript
复制
DELETE FROM `table` WHERE  ( 
// Keep one record and delete rows with duplicate columns b,c,d 
)

colA是主键- AI

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2015-03-22 16:32:42

如果您有主键,则可以:

代码语言:javascript
复制
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;

但是,一个表上的许多删除操作可能效率低下。因此,将数据移动到临时表并重新插入它通常更快:

代码语言:javascript
复制
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;
票数 3
EN

Stack Overflow用户

发布于 2015-03-22 16:30:46

尝尝这个

代码语言:javascript
复制
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
票数 2
EN

Stack Overflow用户

发布于 2015-03-22 16:29:25

试一试如下:

代码语言:javascript
复制
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.ColA
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/29196813

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档