在表中删除某一项的重复值,只保存其中一项
eg : 去除表中重复的URL,只保留一项
Mysql版本:a 和 b 是两个临时表,这是MySQL的一些问题,其他数据库不需要创建新表
se_job_details为表名,j_url 为待去重的数据项。
DELETE FROM se_job_details
WHERE j_url IN (SELECT j_url FROM(select j_url from se_job_details GROUP BY j_url HAVING COUNT(j_url) > 1) a)
AND j_id NOT IN (select * from (SELECT MIN(j_id) FROM se_job_details GROUP BY j_url HAVING COUNT(j_url)>1) b)
SQL server 版本:
DELETE FROM se_job_details
WHERE j_url IN select j_url from se_job_details GROUP BY j_url HAVING COUNT(j_url) > 1)
AND j_id NOT IN SELECT MIN(j_id) FROM se_job_details GROUP BY j_url HAVING COUNT(j_url)>1)