SELECT id,COUNT(1) FROM tablename GROUP BY material_id HAVING COUNT(1) >1
//只获取ID
SELECT id FROM tablename GROUP BY material_id HAVING COUNT(1) >1
2、删除全部重复数据
//查询
SELECT * FROM tablename
WHERE
id in (SELECT * FROM (SELECT id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a)
//删除
DELETE FROM tablename
WHERE
id in (SELECT * FROM (SELECT id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a)
3、删除全部重复数据中最老/新的一条
获取最小/大ID 或最小/大时间的那些数据
MIN(id) - > MAX(id)
道理其实很好理解,满足重复项条件,同时是满足最小/大id或最小/大时间
//根据自增ID
SELECT * FROM tablename
WHERE
material_id in (SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a)
and
id in (SELECT * FROM (SELECT MIN(id) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)
//根据时间
SELECT * FROM tablename
WHERE
material_id in (SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a)
and
id in (SELECT * FROM (SELECT MIN(time) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)
//删除数据
DELETE FROM tablename
WHERE
material_id in (SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a)
and
id in (SELECT * FROM (SELECT MIN(id) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)
4、保留全部重复数据中最老/新的一条,删除其它
使用id not in 即可,排除
//根据自增ID
SELECT * FROM tablename
WHERE
material_id in (SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a)
and
id not in (SELECT * FROM (SELECT MIN(id) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)
//根据时间
SELECT * FROM tablename
WHERE
material_id in (SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a)
and
id not in (SELECT * FROM (SELECT MIN(time) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)
//删除数据
DELETE FROM tablename
WHERE
material_id in (SELECT * FROM (SELECT material_id FROM tablename GROUP BY material_id HAVING COUNT(1) >1) a)
and
id not in (SELECT * FROM (SELECT MIN(id) FROM tablename GROUP BY material_id HAVING COUNT(1) >1) b)