表：

CREATE TABLE Company_Info

(

id? smallint IDENTITY(1,1) ,

username varchar(50) null,

password varchar(50) null,

PRIMARY KEY(id)

)

思路

------------顯示哪些人相同，相同的數(shù)量是多少？

select convert(int,SQRT(count(*))) as countU ,a.username from Company_Info a left join Company_Info b on

a.username=b.username and a.password=b.password

group by a.username having SQRT(count(*)) >1

----------取出所有相同的記錄到一個(gè)表MyRepeat

select c.* into MyRepeat from Company_Info c where c.username in(

select a.username from Company_Info a left join Company_Info b on

a.username=b.username and a.password=b.password

group by a.username having SQRT(count(*)) >1)

-----------在表MyRepeat中找出每個(gè)相同記錄的除最大id之外

select a.id from MyRepeat a where id not in(select max(b.id) from MyRepeat b group by b.username)--得出所有需要?jiǎng)h除的id

-----------

最后得出

delete Company_Info where id in (select h.id from (select c.* from Company_Info c where c.username in(select a.username from Company_Info a left join Company_Info b on a.username=b.username and a.password=b.password group by a.username having SQRT(count(*)) >1) ) h where h.id not in( select max(l.id) from (select k.id,username from (select f.* from Company_Info f where f.username in(select g.username from Company_Info g left join Company_Info o on g.username=o.username and g.password=o.password group by g.username having SQRT(count(*)) >1)) k ) l group by l.username))

posted on 2006-10-18 17:04 jackstudio 閱讀(1549) 評(píng)論(0) ?編輯 ?收藏 所屬分類: Database

總結(jié)

以上是生活随笔為你收集整理的java中删除每一条数据,一条sql语句删除表中所有除ID 不同之外的记录，只留一条。...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。