java中删除每一条数据,一条sql语句删除表中所有除ID 不同之外的记录，只留一条。...

表：

CREATE TABLE Company_Info

(

id  smallint IDENTITY(1,1) ,

username varchar(50) null,

password varchar(50) null,

PRIMARY KEY(id)

)

思路

------------显示哪些人相同，相同的数量是多少？

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

----------取出所有相同的记录到一个表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中找出每个相同记录的除最大id之外

select a.id from MyRepeat a where id not in(select max(b.id) from MyRepeat b group by b.username)--得出所有需要删除的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) 评论(0)  编辑  收藏 所属分类: Database