隨筆分類

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

?

?