帝国系统删除重复文章sql语句

phome_ecms_article_check phome_ecms_article_check_data phome_ecms_article_index 以上三个表,通过id关联,查询phome_ecms_article_check 表title字段,删除重复文章,并保留一篇,剩下phome_ecms_article_check_data表、 phome_ecms_article_index表,根据id,删除相应数据。数据库是mysql,给出执行sql语句

首先,你应该慎重对待删除重复文章的操作,因为一旦操作,那么数据就无法恢复。另外,我假设id字段是这三个表的主键,并且你要删除的重复文章是指phome_ecms_article_check表中title字段重复的文章。

根据你的需求,你可以通过以下步骤操作:

第一步,找出所有重复文章的id,但是保留一篇文章,你可以使用以下SQL查询语句:

SELECT min(id) as id, title 
FROM phome_ecms_article_check 
GROUP BY title 
HAVING count(id) > 1;

这个查询会返回每一个重复标题的最小id。为了删除重复的文章,你需要找出所有的id,不包含这些最小的id。你可以使用以下SQL查询语句:

SELECT a.id 
FROM phome_ecms_article_check a 
LEFT JOIN (
    SELECT min(id) as id, title 
    FROM phome_ecms_article_check 
    GROUP BY title 
    HAVING count(id) > 1
) b 
ON a.id = b.id 
WHERE b.id IS NULL AND a.title IN (
    SELECT title 
    FROM (
        SELECT title 
        FROM phome_ecms_article_check 
        GROUP BY title 
        HAVING count(id) > 1
    ) c
);

第二步,删除phome_ecms_article_checkphome_ecms_article_check_dataphome_ecms_article_index这三个表中重复的文章。你可以使用以下SQL删除语句:

DELETE c, d, e
FROM phome_ecms_article_check c
JOIN phome_ecms_article_check_data d ON c.id = d.id
JOIN phome_ecms_article_index e ON c.id = e.id
WHERE c.id IN (
    SELECT id FROM (
        SELECT a.id
        FROM phome_ecms_article_check a
        LEFT JOIN (
            SELECT min(id) as id, title
            FROM phome_ecms_article_check
            GROUP BY title
            HAVING count(id) > 1
        ) b ON a.id = b.id
        WHERE b.id IS NULL AND a.title IN (
            SELECT title
            FROM (
                SELECT title
                FROM phome_ecms_article_check
                GROUP BY title
                HAVING count(id) > 1
            ) c
        )
    ) AS temp
);

这个SQL删除语句将会删除所有的重复文章,但是会保留每个重复标题的最小id的文章。

这是一个慎重的操作,我建议你首先在测试环境中进行试验,然后再在生产环境中执行。

————————————————

已发布资讯

phome_ecms_article
phome_ecms_article_data_1
phome_ecms_article_index

DELETE c, d, e
FROM phome_ecms_article c
JOIN phome_ecms_article_data_1 d ON c.id = d.id
JOIN phome_ecms_article_index e ON c.id = e.id
WHERE c.id IN (
    SELECT id FROM (
        SELECT a.id
        FROM phome_ecms_article a
        LEFT JOIN (
            SELECT min(id) as id, title
            FROM phome_ecms_article
            GROUP BY title
            HAVING count(id) > 1
        ) b ON a.id = b.id
        WHERE b.id IS NULL AND a.title IN (
            SELECT title
            FROM (
                SELECT title
                FROM phome_ecms_article
                GROUP BY title
                HAVING count(id) > 1
            ) c
        )
    ) AS temp
);

表news 已发布

phome_ecms_news
phome_ecms_news_data_1
phome_ecms_news_index

DELETE c, d, e
FROM phome_ecms_news c
JOIN phome_ecms_news_data_1 d ON c.id = d.id
JOIN phome_ecms_news_index e ON c.id = e.id
WHERE c.id IN (
    SELECT id FROM (
        SELECT a.id
        FROM phome_ecms_news a
        LEFT JOIN (
            SELECT min(id) as id, title
            FROM phome_ecms_news
            GROUP BY title
            HAVING count(id) > 1
        ) b ON a.id = b.id
        WHERE b.id IS NULL AND a.title IN (
            SELECT title
            FROM (
                SELECT title
                FROM phome_ecms_news
                GROUP BY title
                HAVING count(id) > 1
            ) c
        )
    ) AS temp
);

表news 未审核

phome_ecms_news_check
phome_ecms_news_check_data
phome_ecms_news_index

DELETE c, d, e
FROM phome_ecms_news_check c
JOIN phome_ecms_news_check_data d ON c.id = d.id
JOIN phome_ecms_news_index e ON c.id = e.id
WHERE c.id IN (
    SELECT id FROM (
        SELECT a.id
        FROM phome_ecms_news_check a
        LEFT JOIN (
            SELECT min(id) as id, title
            FROM phome_ecms_news
            GROUP BY title
            HAVING count(id) > 1
        ) b ON a.id = b.id
        WHERE b.id IS NULL AND a.title IN (
            SELECT title
            FROM (
                SELECT title
                FROM phome_ecms_news_check
                GROUP BY title
                HAVING count(id) > 1
            ) c
        )
    ) AS temp
);

© 版权声明
THE END
喜欢就支持一下吧
点赞15 分享