select sn, count(*) cnt
into #t
from mytest
group by sndelete from mytest
where sn in
(
select distinct a.sn
from mytest a, mytest b, #t c, #t d
where a.code=b.code and a.ins_no=b.ins_no and a.sn>b.sn
and a.sn=c.sn and b.sn=d.sn and c.cnt=d.cnt
)select * from mytest
into #t
from mytest
group by sndelete from mytest
where sn in
(
select distinct a.sn
from mytest a, mytest b, #t c, #t d
where a.code=b.code and a.ins_no=b.ins_no and a.sn>b.sn
and a.sn=c.sn and b.sn=d.sn and c.cnt=d.cnt
)select * from mytest
where A.code=B.code
and A.ins_no=B.ins_no
and A.sn>B.sn
and not exists (select 1
from mytest
where sn=A.sn
and code+','+ins_no not in(select code+','+ins_no
from mytest
where sn=B.sn
and code=B.code) )
and not exists (select 1
from mytest
where sn=B.sn
and code+','+ins_no not in(select code+','+ins_no
from mytest
where sn=A.sn
and code=A.code) )select * from mytest
code varchar(8),
ins_no varchar(6),
select min(a.sn),a.code,a.ins_no from mytest a,
(select distinct code,ins_no from mytest) as b
where a.code = b.code and a.ins_no = b.ins_no
(select distinct code,ins_no from mytest) as b
where a.code = b.code and a.ins_no = b.ins_no
你的有问题,看下面这个就不能删除了drop table mytest
gocreate table mytest
(
sn int,
code varchar(8),
ins_no varchar(6),
)
goinsert mytest values(1, '01', 'A')
insert mytest values(1, '01', 'B')
insert mytest values(1, '02', 'B') --增加一条
insert mytest values(2, '02', 'A')
insert mytest values(2, '02', 'D')
insert mytest values(3, '01', 'A')
insert mytest values(3, '01', 'B')
insert mytest values(3, '02', 'B') --增加一条
insert mytest values(4, '03', 'A')
insert mytest values(4, '03', 'B')
insert mytest values(4, '03', 'C')
insert mytest values(5, '03', 'B')
insert mytest values(5, '03', 'C')
insert mytest values(6, '02', 'A')
insert mytest values(6, '02', 'C')
insert mytest values(6, '02', 'D')
godelete A from mytest A,mytest B
where A.code=B.code
and A.ins_no=B.ins_no
and A.sn>B.sn
and not exists (select 1
from mytest
where sn=A.sn
and code+','+ins_no not in(select code+','+ins_no
from mytest
where sn=B.sn
and code=B.code) )
and not exists (select 1
from mytest
where sn=B.sn
and code+','+ins_no not in(select code+','+ins_no
from mytest
where sn=A.sn
and code=A.code) )select * from mytest/*--测试结果
sn code ins_no
----------- -------- ------
1 01 A
1 01 B
1 02 B
2 02 A
2 02 D
3 01 A
3 01 B
3 02 B
4 03 A
4 03 B
4 03 C
5 03 B
5 03 C
6 02 A
6 02 C
6 02 D(所影响的行数为 16 行)
--*/
create function f_str(@sn int)
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+code+','+ins_no
from mytest where sn=@sn
order by code,ins_no
return(@re)
end
go--调用自定义函数,实现删除
delete mytest
from mytest a left join(
select min_sn=min(sn),max_sn=max(sn)
from mytest group by dbo.f_str(sn)
having min(sn)<>max(sn)
) b on a.sn=b.max_sn
where b.max_sn is not null
go
drop function f_str
gocreate table mytest
(
sn int,
code varchar(8),
ins_no varchar(6),
)
goinsert mytest values(1, '01', 'A')
insert mytest values(1, '01', 'B')
insert mytest values(1, '02', 'B') --增加一条
insert mytest values(2, '02', 'A')
insert mytest values(2, '02', 'D')
insert mytest values(3, '01', 'A')
insert mytest values(3, '01', 'B')
insert mytest values(3, '02', 'B') --增加一条
insert mytest values(4, '03', 'A')
insert mytest values(4, '03', 'B')
insert mytest values(4, '03', 'C')
insert mytest values(5, '03', 'B')
insert mytest values(5, '03', 'C')
insert mytest values(6, '02', 'A')
insert mytest values(6, '02', 'C')
insert mytest values(6, '02', 'D')
insert mytest values(7, '01', 'A') --增加一组
insert mytest values(7, '01', 'B')
insert mytest values(7, '02', 'B')
go--创建处理的自定义函数
create function f_str(@sn int)
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+code+','+ins_no
from mytest where sn=@sn
order by code,ins_no
return(@re)
end
go--调用自定义函数,实现删除
delete mytest
from mytest a left join(
select min_sn=min(sn),max_sn=max(sn)
from mytest group by dbo.f_str(sn)
having min(sn)<>max(sn)
) b on a.sn=b.max_sn
where b.max_sn is not null
go--处理结果
select * from mytest/*--测试结果sn code ins_no
----------- -------- ------
1 01 A
1 01 B
1 02 B
2 02 A
2 02 D
3 01 A
3 01 B
3 02 B
4 03 A
4 03 B
4 03 C
5 03 B
5 03 C
6 02 A
6 02 C
6 02 D(所影响的行数为 16 行)
--*/
--调用自定义函数,实现删除delete a
from mytest a where not exists (select 1 from (select min_sn=min(sn) from mytest group by dbo.f_str(sn)) c where a.sn=c.min_sn)go
delete A from mytest A,mytest B
where A.code=B.code
and A.ins_no=B.ins_no
and A.sn>B.sn
and not exists (select 1
from mytest
where sn=A.sn
and code+','+ins_no not in(select code+','+ins_no
from mytest
where sn=B.sn
) )
and not exists (select 1
from mytest
where sn=B.sn
and code+','+ins_no not in(select code+','+ins_no
from mytest
where sn=A.sn
) )