Question

删除数据库表（无主键）中重复的记录，sql语句怎么写？

删除数据库表（无主键）中重复的记录，sql语句怎么写？ 示例如下：

如何变成：





Answer 1

这是我用的，你可以根据你的数据表改改
use XLERP
--1、查询重复数据
SELECT cInvCode, cAcc_Id,count(*)FROM Attribute GROUP BY cInvCode, cAcc_Id HAVING count(*) > 1
--2、选择重复值到临时表
SELECT cInvCode, cAcc_Id,bSale, bPurchase, bSelf, bComsume, bProducing, bService, bAccessary, bInvType, bPropertyCheck, bPlanInv, bProxyForeign, bATOModel, bCheckItem, bPTOModel, bMPS, bROP, bCutMantissa, bInvModel, iInvNCost, iTopSum, iLowSum, iSafeNum, fOutExcess, cInvABC, dSDate, dEDate, cCreatePerson, cModifyPerson, dModifyDate,col3=count(*) INTO holdkey FROM Attribute GROUP BY cInvCode, cAcc_Id,bSale, bPurchase, bSelf, bComsume, bProducing, bService, bAccessary, bInvType, bPropertyCheck, bPlanInv, bProxyForeign, bATOModel, bCheckItem, bPTOModel, bMPS, bROP, bCutMantissa, bInvModel, iInvNCost, iTopSum, iLowSum, iSafeNum, fOutExcess, cInvABC, dSDate, dEDate, cCreatePerson, cModifyPerson, dModifyDate HAVING count(*) > 1
--3、选择重复的行放入临时表中，以清除进程中的重复值
SELECT DISTINCT Attribute.* INTO holddups FROM Attribute, holdkey WHERE Attribute.cInvCode = holdkey.cInvCode AND Attribute.cAcc_Id = holdkey.cAcc_Id
--4、验证 holddups 中的各个键是否唯一
SELECT cInvCode, cAcc_Id, count(*) FROM holddups GROUP BY cInvCode, cAcc_Id
--5、从原始表中删除重复的行
DELETE Attribute FROM Attribute, holdkey WHERE Attribute.cInvCode = holdkey.cInvCode AND Attribute.cAcc_Id = holdkey.cAcc_Id
--6、将唯一行放回原始表中
INSERT Attribute SELECT * FROM holddups
--7、删除临时表
drop table holddups
drop table holdkey

Answer 2

1, CREATE TABLE new_table S_b (SELECT distinct * FROM old_table);
2, Delete old_table
3, Rename new_table