--建立个function
create table mytest
(
sn int,
code varchar(8),
ins_no varchar(6),
)
goinsert 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(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(5, '03', 'E')
insert mytest values(6, '02', 'A')
insert mytest values(6, '02', 'C')
insert mytest values(6, '02', 'D')
insert mytest values(7, '02', 'C')
insert mytest values(7, '02', 'D')
insert mytest values(8, '02', 'C')
insert mytest values(8, '02', 'D')
go--select * from mytest
CREATE function MyFc_1(@content1 varchar(50))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+code+ins_no from mytest where sn=@content1 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.myfc_1(sn))
----删除实验环境
drop function MyFc_1
drop table mytest
--
-- 字段SN是用来分组的。如果不同SN下的记录完全一样(除了SN本身),
-- 包括记录条数、字段内容,则我希望只保留一个分组,
-- 例子中分组7和8满足这种情况,所以把分组7(或8)剔除掉。
-- 能不能用一个查询语句做到?
create table mytest
(
sn int,
code varchar(8),
ins_no varchar(6),
)
goinsert 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(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(5, '03', 'E')
insert mytest values(6, '02', 'A')
insert mytest values(6, '02', 'C')
insert mytest values(6, '02', 'D')
insert mytest values(7, '02', 'C')
insert mytest values(7, '02', 'D')
insert mytest values(8, '02', 'C')
insert mytest values(8, '02', 'D')
go--select * from mytest
CREATE function MyFc_1(@content1 varchar(50))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+code+ins_no from mytest where sn=@content1 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.myfc_1(sn))
----删除实验环境
drop function MyFc_1
drop table mytest
--
-- 字段SN是用来分组的。如果不同SN下的记录完全一样(除了SN本身),
-- 包括记录条数、字段内容,则我希望只保留一个分组,
-- 例子中分组7和8满足这种情况,所以把分组7(或8)剔除掉。
-- 能不能用一个查询语句做到?
Jianli2004(健力) :不错,不过最好贴到水版。
然后我晕了,实在不想写这么低效率的语句,就停手了。。 select sn from mytest t1
where sn not in (select sn3 from
(
select t3.sn sn3,t4.sn sn4,t3.code tc3 ,t3.ins_no ti3,t4.code tc4 ,t4.ins_no ti4
from mytest t3 full join mytest t4 on t3.code=t4.code and t3.ins_no=t4.ins_no and t3.sn<>t4.sn
and (select count(*) from mytest t6 where t6.sn=t3.sn and t6.code=t3.code and t6.ins_no=t3.ins_no)
=(select count(*) from mytest t6 where t6.sn=t4.sn and t6.code=t4.code and t6.ins_no=t3.ins_no)
) t5 where t5.sn3=t1.sn and t5.sn4 is null --and sn3 <sn4
)
from mytest t3 full join mytest t4 on t3.code=t4.code and t3.ins_no=t4.ins_no and t3.sn<>t4.sn
and (select count(*) from mytest t6 where t6.sn=t3.sn and t6.code=t3.code and t6.ins_no=t3.ins_no)
=(select count(*) from mytest t6 where t6.sn=t4.sn and t6.code=t4.code and t6.ins_no=t3.ins_no)
和自己Join。。
where 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
) )
and not exists(select 1 from
(select code+','+ins_no as cod,count(*) con
from mytest
where sn=A.sn
group by code+','+ins_no) C,
(select code+','+ins_no as cod,count(*) con
from mytest
where sn=B.sn
group by code+','+ins_no) D
where C.cod=D.cod and C.con<>D.con)select * from mytest
他的分析和做法
两个要点,
一, 要对数据按sn进行分组
二, 每组数据相同的充分必要条件为件数相等且相同记录件数相等且记录相同
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)))) ---------------------------------------------------------------------------
我的分析和做法:
两组纪录相同的充分必要条件是
1.根据sn分组纪录数相同
2.根据sn,code+ins_no分组纪录数相同
3.code,ins_no所在字段值相同
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,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.num
)
order by sn --min换成max也可以。
学习!up
就不存在了。万万没想到。
你们往往会出奇制胜。
PFPF!
你们join用的是求同,
而博士和我的是排除法。
select A.sn from mytest A,mytest B
where a.code=B.code
and a.ins_no=B.ins_no
and A.sn<>B.sn
and (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))
or exists(select 1 from mytest where sn=A.sn and code+','+ins_no not in
(select code+','+ins_no from mytest where sn=A.sn))
or exists(select 1 where (select count(*) from mytest where sn=A.sn and code+','+ins_no=A.code+','+A.ins_no)<>
(select count(*) from mytest where sn=B.sn and sn<A.sn and code+','+ins_no=A.code+','+A.ins_no)
)))