創建臨時表
create table tmp_dur as
select a.shop_id,a.city_id,a.product_id,max(a.id) as mid from 51fanli_tuandh_item as a group by a.shop_id,a.city_id,a.product_id having count(*) > 1
查詢聯合表
SELECT a.shop_id,a.city_id,a.product_id from tmp_dur a , 51fanli_tuandh_item b where a.shop_id = b.shop_id and a.city_id = b.city_id and a.product_id = b.product_id
刪除重復項目
delete from 51fanli_tuandh_item
where 51fanli_tuandh_item.id != (
SELECT b.mid from tmp_dur b
where 51fanli_tuandh_item.city_id = b.city_id and 51fanli_tuandh_item.shop_id = b.shop_id and 51fanli_tuandh_item.product_id = b.product_id
);
刪除臨時表
drop tmp_dur;
添加唯一
ALTER TABLE `51fanli_tuandh_item` ADD UNIQUE (
`shop_id` ,
`city_id` ,
`product_id`
);