create table mytest
(sn int,code varchar(8),ins_no varchar(6))
go
insert mytest values(1, '01', 'A')
insert mytest values(1, '01', 'A')
insert mytest values(1, '02', 'B')
insert mytest values(2, '01', 'A')
insert mytest values(2, '02', 'B')
insert mytest values(2, '02', 'B')
insert mytest values(7, '02', 'C')
insert mytest values(7, '02', 'D')
insert mytest values(8, '02', 'C')
insert mytest values(8, '02', 'D')
insert mytest values(9, '01', 'A')
insert mytest values(9, '01', 'A')
insert mytest values(10, '02', 'B')
insert mytest values(44, '03', 'A')
insert mytest values(44, '03', 'B')
insert mytest values(45, '03', 'A')
insert mytest values(45, '03', 'C')
insert mytest values(46, '03', 'B')
insert mytest values(46, '03', 'C')
insert mytest values(2222, '02', 'B')
go
select * from mytest
go

剔除条件：
字段SN是用来分组的。如果不同SN下的记录完全一样（除了SN本身），包括记录条数、字段内容，则我希望只保留一个分组，例子中分组7和8满足这种情况，所以把分组7(或8)剔除掉。

----------------------------------------------------------------------------------------------
/*两组纪录相同的充分必要条件是
横向
1根据sn分组纪录数相同
2根据sn,code+ins_no分组纪录数相同
3code,ins_no所在纪录相同
纵向
4根据sn分组code,ins_no的ASCII值的和相同*/

select * from mytest where sn in(
select min(a.sn)as sn
from mytest a,(select sn,code+ins_no as series,count(1) as num from mytest b group by sn,code+ins_no)b, (select sn,sum(code+ascii(ins_no))as ascii,count(*) as num from mytest group by sn) c
where a.sn=b.sn and a.sn=c.sn
group by a.code,a.ins_no,b.series,b.num,c.ascii,c.num
)
order by sn--min换成max也可以

--以下是其他几位朋友的答案
----------------------------------------------------------------------------------------------
select * from mytest t1
where sn in
(select sn from
(select sn, count(*) as cnt from mytest group by sn) t1 --MYGROUP
where not exists(
select sn from
(select sn, count(*) as cnt from mytest group by sn) t2 --MYGROUP
where t2.sn < t1.sn and t1.cnt=t2.cnt
and not exists(
select null from
(select sn, code, ins_no, count(*) as cnt from mytest group by sn, code, ins_no) t3 --MYITEM
where t3.sn = t1.sn
and not exists(
select null from
(select sn, code, ins_no, count(*) as cnt from mytest group by sn, code, ins_no) t4 --MYITEM
where t4.sn = t2.sn
and t4.code=t3.code
and t4.ins_no=t3.ins_no
and t4.cnt=t3.cnt))))
----------------------------------------------------------------------------------------------
select *
from mytest a
where not exists (select 1 from
( select a.sn as s_sn, b.sn as l_sn
from mytest a, mytest b
where a.code = b.code
and a.ins_no = b.ins_no
and a.sn < b.sn
group by a.sn, b.sn
having count(*) = (select count(*)
from mytest c
where a.sn = c.sn)
and count(*) = (select count(*)
from mytest d
where b.sn = d.sn)
and count(distinct a.code + a.ins_no)
= (select count(distinct c.code + c.ins_no)
from mytest c
where a.sn = c.sn)
and count(distinct a.code + a.ins_no)
= (select count(distinct d.code + d.ins_no)
from mytest d
where b.sn = d.sn)
) set_e
where a.sn = set_e.l_sn)
----------------------------------------------------------------------------------------------
--将相同sn的纪录的code,ins_no串联成一个字符串，作为以后group by的依据。
create function test(@sninput varchar(50))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+code+ins_no from mytest where sn=@sninput order by code,ins_no
return @str
end
go
----执行可得到结果
select * from mytest where sn in (select min(sn) as sn from mytest group by dbo.test(sn))