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

这是我用的，你可以根据你的数据表改改

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

1、先将不重复的数据查询出来并放到临时表中：

select distinct * into #temp from 数据库

2、删除数据库表中的内容。

3、然后将临时表的内容插入数据库表中保存即可。

1.先把数据表的去除重复的数据放到临时表中

select distinct * into #t from table --distinct 是sqlserver 里面用来去重的

2.把数据表里的数据清空

truncate table dbo.[table]

3.把临时表里的数据插入到数据表里

insert into table select * from #t

4.删除临时表

drop table #t

---