急求一条SQL语句，有难度，数据量很大，希望能有好的方法!谢谢！

Question

这个能符合你的要求吗？--函数
create function fn_adsva(@tlsq int)
returns varchar(10)
as
begin
declare @ret varchar(10)
select @ret=''
select @ret=@ret+cuno from adsva where tlsq=@tlsq
return @ret
end--调用函数
select cuno,DB.dbo.fn(cuno) from tlsq group by cuno having count(*)>1

Answer 1

select distinct cuno into #t from adsva group by cuno having count(cuno)>2
select tlsq,cuno,tram from adsva where cuno in(select cuno from #t) group by tlsq,cuno,tram
Drop table #t不知道你是数据表是什么数量级

Answer 2

create table adsva(tlsq int,cuno varchar(1),tram decimal(10,2),dt smalldatetime,re varchar(255))
insert adsva
select 1,'A',     -0.25 ,'2005/12/01','test1' union all
select 1,'B',   0.25,'2005/12/01','test2' union all
select 2,'C', 1.25,'2005/12/02','test3' union all
select 3,'A',     3.25,'2005/12/02','test4' union all
select 3,'B',     -3.25,'2005/12/03','test5' union all
select 4,'A',     6.05,'2005/12/03','test6' union all
select 4,'B',     -6.05,'2005/12/03','test7' union all
select 5,'C',     -0.25,'2005/12/04','test8' union all
select 5,'D',     0.25,'2005/12/04','test9' union all
select 6,'B',     0.25,'2005/12/05','test10' union all
select 6,'E',     -0.25,'2005/12/05','test11' union all
select 7,'A',     0.25,'2005/12/05','test12' union all
select 8,'D',     -0.25,'2005/12/06','test13' union all
select 8,'E',     0.25,'2005/12/06','test14' union all
select 9,'A',     2.25,'2005/12/07','test15' union all
select 9,'C',     -2.25,'2005/12/07','test16' union all
select 10,'B',     0.25,'2005/12/07','test17'create view v_adsva 
as
select a.tlsq,a.cuno,newno=a.cuno+b.cuno,a.tram,a.dt,a.re
from ( select * from adsva 
       where tlsq in (select tlsq from adsva group by tlsq having count(tlsq)>1)) a,
     ( select * from adsva 
       where tlsq in (select tlsq from adsva group by tlsq having count(tlsq)>1)) b
where a.tlsq=b.tlsq and a.cuno<>b.cuno
goselect tlsq,cuno,tram,dt,re from v_adsva
where newno in (select newno from v_adsva group by newno having count(newno)>=3)/*
tlsq        cuno tram         dt                                                     re                                                                                                                                                                                                                                                          
----------- ---- ------------ ------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           A    -.25         2005-12-01 00:00:00                                    test1
1           B    .25          2005-12-01 00:00:00                                    test2
3           A    3.25         2005-12-02 00:00:00                                    test4
3           B    -3.25        2005-12-03 00:00:00                                    test5
4           A    6.05         2005-12-03 00:00:00                                    test6
4           B    -6.05        2005-12-03 00:00:00                                    test7*/

Answer 3

我的表中原有一个dt的字段，本来是对dt,acno等是做了索引的，现在我创建视图的话，我的索引就不起作用了，这样速度就会很慢了，不知有什么好的办法？

Answer 4

现在刚刚发现，单单tlsq还是不行的，还要再加上日期，才能判定是同一笔交易！
表adsva 
trdt char(8)
tlsq char(10)
cuno char(10)
tram decimal  --金额trdt      tlsq    cuno   tram  …………………………（其它字段）
20051210   1        A     -0.25                                
20051210   1        B     0.25                                 
20051210   2        C     1.25                                 
20051210   3        A     3.25                                 
20051210   3        B     -3.25                                
20051210   4        A     6.05                                 
20051210   4        B     -6.05                                
20051210   5        C     -0.25                                
20051210   5        D     0.25                                 
20051210   6        B     0.25                                 
20051210   6        E     -0.25                                
20051210   7        A     0.25                                 
20051210   8        D     -0.25                                
20051210   8        E     0.25                                 
20051210   9        A     2.25                                 
20051210   9        C     -2.25                                
20051210   10       B     0.25                                 
20051211   1        A     -0.25                                
20051211   1        F     0.25                                 
20051211   2        C     1.25                                 
20051211   3        A     3.25                                 
20051211   3        B     -3.25                                
20051211   4        A     6.05                                 
20051211   4        P     -6.05                                
20051211   5        C     -0.25                                
20051211   5        D     0.25                                 
20051211   6        B     0.25                                 
20051211   6        E     -0.25                                
20051211   7        A     0.25                                 
20051211   8        D     -0.25                                
20051211   8        E     0.25                                 
20051211   9        A     2.25                                 
20051211   9        M     -2.25                                
20051211   10       Q     0.25    
这如何实现呢？mislrb(aben) 的方法我改成存储过程，速度就很快了，因日期上有索引谢谢！

Answer 5

其实将我的SQL语句稍加变化就可以实现你的新要求的，楼主还是多尝试才有进步的，create table adsva(trdt varchar(8),tlsq varchar(10),cuno varchar(10),tram decimal(10,2))
insert adsva
select '20051210','1','A',     -0.25 union all
select '20051210','1','B',     0.25  union all                                
select '20051210','2','C',     1.25  union all                                
select '20051210','3','A',     3.25  union all                                
select '20051210','3','B',     -3.25  union all                               
select '20051210','4','A',     6.05  union all                                
select '20051210','4','B',     -6.05  union all                               
select '20051210','5','C',     -0.25  union all                               
select '20051210','5','D',     0.25  union all                                
select '20051210','6','B',     0.25  union all                                
select '20051210','6','E',     -0.25  union all                               
select '20051210','7','A',     0.25  union all                                
select '20051210','8','D',     -0.25  union all                               
select '20051210','8','E',     0.25  union all                                
select '20051210','9','A',     2.25  union all                                 
select '20051210','9','C',     -2.25  union all                               
select '20051210','10','B',     0.25  union all                                 
select '20051211','1','A',     -0.25  union all                               
select '20051211','1','F',     0.25  union all                                
select '20051211','2','C',     1.25  union all                                
select '20051211','3','A',     3.25  union all                                
select '20051211','3','B',     -3.25  union all                               
select '20051211','4','A',     6.05  union all                                
select '20051211','4','P',     -6.05  union all                               
select '20051211','5','C',     -0.25  union all                               
select '20051211','5','D',     0.25  union all                                
select '20051211','6','B',     0.25  union all                                
select '20051211','6','E',     -0.25  union all                               
select '20051211','7','A',     0.25  union all                                
select '20051211','8','D',     -0.25  union all                               
select '20051211','8','E',     0.25  union all                                
select '20051211','9','A',     2.25  union all                                
select '20051211','9','M',     -2.25  union all                               
select '20051211','10','Q',     0.25 
go   create view v_adsva 
as
select a.trdt,a.tlsq,a.cuno,newno=a.cuno+b.cuno,a.tram
from ( select * from adsva 
       where trdt+tlsq in (select trdt+tlsq from adsva group by trdt+tlsq having count(trdt+tlsq)>1)) a,
     ( select * from adsva 
       where trdt+tlsq in (select trdt+tlsq from adsva group by trdt+tlsq having count(trdt+tlsq)>1)) b
where a.trdt+a.tlsq=b.trdt+b.tlsq and a.cuno<>b.cuno
goselect trdt,tlsq,cuno,tram from v_adsva
where newno in (select newno from v_adsva group by newno having count(newno)>=3)