mysql语句之批量修改、查询重复、删除重复

1、批量修改

UPDATE '表名' SET
    listdir = CASE aid
        WHEN 1000 THEN '/labels/2840'
        WHEN 2851 THEN '/labels/2841'
    END
WHERE aid IN (2850,2851)

2、查询重复

select keyword,count(*) as count from '表名' group by keyword having count>1;

3、删除重复

DELETE FROM '表名' WHERE keyword IN (SELECT keyword FROM (SELECT keyword FROM '表名' GROUP BY keyword HAVING COUNT(keyword)>1) e)
 AND aid NOT IN (SELECT aid FROM (SELECT MIN(aid) AS aid FROM '表名' GROUP BY keyword HAVING COUNT(keyword)>1) t)