我想查询一个表,如表1
id 数据a 数据b 数据c
1 4 3 5
2 5 4 3
3 1 4 7
4 1 7 4
5 7 1 4
6 0 6 6 我想查询表中的重复数据情况,id为序号,是自动生成的,数据a+数据b+数据c=12,三个字段的数据不论顺序前后,只要是三个数相同都算重复,如下:
1 4 3 5
2 5 4 3
怎样用查询语句统计重复记录,并分组列出?谢谢!
id 数据a 数据b 数据c
1 4 3 5
2 5 4 3
3 1 4 7
4 1 7 4
5 7 1 4
6 0 6 6 我想查询表中的重复数据情况,id为序号,是自动生成的,数据a+数据b+数据c=12,三个字段的数据不论顺序前后,只要是三个数相同都算重复,如下:
1 4 3 5
2 5 4 3
怎样用查询语句统计重复记录,并分组列出?谢谢!
希望有用
==================================================
/*FUNCTION
CREATE FUNCTION dbo.selectpart (@cola varchar(50),@colb varchar(50),@colc varchar(50),@id int)
RETURNS int AS
BEGIN
if @cola ='' or @colb = '' or @colc = ''
begin
return 0
end
if exists(select * from xjj where id <> @id and (cola = @cola or cola = @colb or cola = @colc) and (colb = @cola or colb = @colb or colb = @colc) and (colc = @cola or colc = @colb or colc = @colc))
begin
return 1
end
else
begin
return 0
end
return 0
END*/
create table xjj(id int,cola int,colb int,colc int)/*
id 数据a 数据b 数据c
1 4 3 5
2 5 4 3
3 1 4 7
4 1 7 4
5 7 1 4
6 0 6 6
*/
insert into xjj select 1,4,3,5
union select 2,5,4,3
union select 3,1,4,7
union select 4,1,7,4
union select 5,7,1,4
union select 6,0,6,6declare @i int
set @i = 0
select * from xjj where dbo.selectpart(cola,colb,colc,id)=1
drop table xjj
--drop function dbo.selectpart
--这段程序会重复输出一次重复项,有时间再改
declare @k1 int,@k2 int,@k3 int,@k4 int
declare @j1 int,@j2 int,@j3 int,@j4 int
select * into #j from test
declare rs cursor for
select * from test
declare rsj cursor for
select * from #j
open rs
fetch next from rs
into @k1,@k2,@k3,@k4
while @@fetch_status=0
begin
open rsj
fetch next from rsj
into @j1,@j2,@j3,@j4
while @@fetch_status=0
begin
if @k1=@j1
begin
fetch next from rsj
into @j1,@j2,@j3,@j4
end
else
if @k2+@k3+@k4=@j2+@j3+@j4
begin
print cast(@k1 as varchar)+' '+cast(@k2 as varchar)+' '+cast(@k3 as varchar)+' '+cast(@k4 as varchar)
print cast(@j1 as varchar)+' '+cast(@j2 as varchar)+' '+cast(@j3 as varchar)+' '+cast(@j4 as varchar)
fetch next from rsj
into @j1,@j2,@j3,@j4
end
else
begin
fetch next from rsj
into @j1,@j2,@j3,@j4
end
end
close rsj
fetch next from rs
into @k1,@k2,@k3,@k4
end
drop table #j
deallocate rsj
close rs
deallocate rs
例如 :
id 数据a 数据b 数据c 数据abc
1 4 3 5 435
2 5 4 3 543
3 1 4 7 147
4 1 7 4 174
5 7 1 4 714
6 0 6 6 066定义的函数.
初步:
第一步查找相同 如果大于0则重复.
第二步将三个数字排列组合成新的数据,并查找如果大于0则重复
渐进:
结合第一步,第二步,做循环查找到结束.
例如 :
id 数据a 数据b 数据c 数据abc 数据acb 数据bac 数据bca 数据cab 数据cba
1 4 3 5 435
2 5 4 3 543
3 1 4 7 147
4 1 7 4 174
5 7 1 4 714
6 0 6 6 066
在abc/acb/bac/bca/cab/cba这些数据中找相同的,如果没有则不重复.