//////////////////////////////////////
以下为邮件地址重复邮件 先找出再删除吧,参考
SELECT *
FROM OLYEmail
WHERE (MAIL_ADDRESS IN
(SELECT mail_address
FROM OLYEmail
GROUP BY mail_address
HAVING COUNT(*) > 1))
//////////////////////////////////////////////////////
SELECT *
FROM dbo.OLYEmail INNER JOIN
dbo.OLYEmailGroup ON
dbo.OLYEmail.GROUPID = dbo.OLYEmailGroup.GROUPID
WHERE (dbo.OLYEmail.MAIL_ADDRESS IN
(SELECT mail_address
FROM OLYEmail
GROUP BY mail_address
HAVING COUNT(*) > 1))
insert A select 1,'a'
insert A select 1,'a'
insert A select 2,'h'
insert A select 2,'h'
insert A select 2,'h'
insert A select 2,'h'
insert A select 3,'j'
insert A select 3,'j'
insert A select 3,'j'
insert A select 3,'j'
insert A select 3,'j'
insert A select 3,'j'
select identity(int,1,1) as T_id,* into #T from A
go
delete from A
go
insert A select T.id,T.Name from #T T
where (select count(1) from #T where T.id=id and T.Name=Name and T.T_id>=T_id)%2=0
go
insert into #b
select '1','a' union all
select '1','a' union all
select '2','h' union all
select '2','h' union all
select '2','h' union all
select '2','h' union all
select '3','f' union all
select '3','f' union all
select '3','f' union all
select '3','f' union all
select '3','f' union all
select '3','f' union all
select '4','j' union all
select '4','j' union all
select '4','j'
--select * from b
select id=identity(int,1,1),* into #t from #b
--select * from #t
select * ,(select count(*)+1 from #t where a=a.a and a.id>id) cid into #t1 from #t a
--select * from #t1
select * from #t1 a where
cid<=(select max(cid)/2 from #t1 where a.a=a )
drop table #b
drop table #t
drop table #t1
---------------------------------
(所影响的行数为 15 行)
(所影响的行数为 15 行)
(所影响的行数为 15 行)
id a b cid
----------- ----------- -------------------- -----------
1 1 a 1
3 2 h 1
4 2 h 2
7 3 f 1
8 3 f 2
9 3 f 3
13 4 j 1
select id1=identity(int,1,1),* INTO # FROM T1 ORDER BY ID,NAME
delete t from # t where (select count(1) from # where id=t.id and name=t.name and id1<=t.id1) %2=0
truncate table t1
insert into t1 select id,name from #
select * from t1
create table xhb_tset (
id varchar(40) not null,
name varchar(40)
)
go
declare @id varchar(40)
declare id_name cursor
for
select id from xhb_tset0 group by id
open id_name
fetch next from id_name into @id
while @@fetch_status=0
begin
insert into xhb_tset
select top 50 PERCENT * from xhb_tset0
where id=@id
fetch next from id_name into @id
end
close id_name
deallocate id_name