mysql根据多列去除重复数据

2022-03-06 08:31:55

　　首先我的数剧是这样的：

　　我想根据name1、relation和name2判断如果重复的话删除重复的数据且保留id最小的一条数据，于是写了下面的sql语句

DELETE
FROM star_relation
WHERE (name1,relation,name2)IN
                        (SELECT
                                name1,
                                relation,
                                name2
                              FROM star_relation
                              GROUP BY name1,relation,name2
                              HAVING COUNT( * ) > 1)
    AND id NOT IN
                                        (SELECT
                        MIN(id) AS id
                        FROM star_relation
                        GROUP BY name1,relation,name2
                        HAVING COUNT( * ) > 1)

结果报了以下错误:[Err] 1093 - You can‘t specify target table ‘star_relation‘ for update in FROM clause

查找资料得知：大概是因为不能直接在查询的语句中进行操作。所以解决办法就是:将查询包装一层，于是有了下面的代码：（亲测有效）

DELETE
FROM star_relation
WHERE (name1,relation,name2)IN(SELECT
                          name1,
                          relation,
                          name2
                        FROM (SELECT
                                name1,
                                relation,
                                name2
                              FROM star_relation
                              GROUP BY name1,relation,name2
                              HAVING COUNT( * ) > 1) a)
    AND id NOT IN (SELECT id FROM
                                                (SELECT
                          MIN(id) AS id
                        FROM star_relation
                        GROUP BY name1,relation,name2
                        HAVING COUNT( * ) > 1)b)

mysql根据多列去除重复数据

码农公寓

相关文章