随笔分类

表：
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))