1.删除数据库重复数据，保留id最小的数据
 
DELETE FROM c_customer_product
WHERE fid IN
(
SELECT fid FROM
(
SELECT fid FROM c_customer_product
WHERE (fcustomer_id,fproduct_id,fis_delete) IN
(SELECT fcustomer_id,fproduct_id,fis_delete
FROM c_customer_product GROUP BY fcustomer_id,fproduct_id,fis_delete HAVING COUNT(*)>1)
AND fid NOT IN
(SELECT MIN(fid) FROM c_customer_product GROUP BY fcustomer_id,fproduct_id,fis_delete HAVING COUNT(*)>1)
) fidresult
)
 
2.查找某个表中某几条记录完全相同的数据
 
如 ：查询 c_customer表中fdoctor_id,fhospital_id,fis_delete数据一样的记录
SELECT fid FROM
(
SELECT fid FROM c_customer
WHERE (fdoctor_id,fhospital_id,fis_delete) IN
(SELECT fdoctor_id,fhospital_id,fis_delete
FROM c_customer GROUP BY fdoctor_id,fhospital_id,fis_delete HAVING COUNT(*)>1)
AND fid NOT IN
(SELECT MIN(fid) FROM c_customer GROUP BY fdoctor_id,fhospital_id,fis_delete HAVING COUNT(*)>1)
) fidresult

 

版权声明：本文为liuchq原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/liuchq/p/10496463.html