問(wèn)題描述

根據(jù)一個(gè)字段查找另一個(gè)字段重復(fù)的數(shù)據(jù)？并刪除相同的記錄，保留其中一個(gè)。

數(shù)據(jù)庫(kù)表：product_code_relate_titletext

字段：id,search_id,product_code,raw_title

需求：根據(jù)product_code重找raw_title重復(fù)的數(shù)據(jù)？（已實(shí)現(xiàn)）

需求：根據(jù)product_code重找raw_title重復(fù)的數(shù)據(jù)，并且刪除相同的數(shù)據(jù)，保留id最少的記錄即可。

（注意：刪除的是product_code下raw_title重復(fù)的數(shù)據(jù)）

select * from product_code_relate_titletext p where (select count(1) from product_code_relate_titletext where product_code=p.product_code and raw_title=p.raw_title)>1

問(wèn)題解答

delete from product_code_relate_titletext where id not in (select * from (select min(id) from product_code_relate_titletext group by product_code,raw_title having count(*) > 1) as b);

delete from product_code_relate_titletext where id in ( select a.id from (select * from product_code_relate_titletext where id not in (select min(id) from product_code_relate_titletext group by product_code,raw_title) ) a)回答3：

沒(méi)有您的數(shù)據(jù)，我這里給一段代碼供您參考：

刪除相同Name除ID最小的記錄外的其他記錄

-- ------------------------------ Table structure for t_team-- ----------------------------DROP TABLE IF EXISTS `t_team`;CREATE TABLE `t_team` ( `id` int(11) NOT NULL, `user_name` varchar(50) DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of t_team-- ----------------------------INSERT INTO `t_team` VALUES (’1’, ’A’);INSERT INTO `t_team` VALUES (’2’, ’B’);INSERT INTO `t_team` VALUES (’3’, ’B’);INSERT INTO `t_team` VALUES (’4’, ’A’);INSERT INTO `t_team` VALUES (’5’, ’C’);INSERT INTO `t_team` VALUES (’6’, ’C’);-- ------------------------------ 刪除NAME重復(fù)的記錄，保留ID最小的記錄-- ----------------------------DELETE FROM t_team WHERE id NOT IN ( SELECT min_id FROM ( SELECT MIN(id) AS min_id FROM t_team GROUP BY user_name ) B )