select a.Gather_no,c.bargain_name
from TGather a,TBargainGather b,TBargain c
where a.Gather_no=b.Gather_no and b.bargain_id=c.bargain_id
from TGather a,TBargainGather b,TBargain c
where a.Gather_no=b.Gather_no and b.bargain_id=c.bargain_id
调试欢乐多
@Gather_no varchar(30)
)
returns varchar(1000)
as
begin
declare @r varchar(1000)
set @r=''
select @r=@r+','+cast(bargain_id as varchar(30)) from 合同回款表 where Gather_no =@Gather_no
if @r<>'' set @r=right(@r,len(@r)-1)
return @r
end
go--查询
select Gather_no,dbo.fn_total_bargain(Gather_no) as total_bargain
from 合同回款表
group by Gather_no
合同编号要放在一个字段中,那就需要先建立一个函数了
create function f_getname(@Gather_no int)
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+','+c.bargain_name as
from TGather a,TBargainGather b,TBargain c
where a.Gather_no=b.Gather_no and b.bargain_id=c.bargain_id and a.Gather_no=@Gather_no
return substring(@str,2,len(@str))
end
go然后建立视图create view v_test
as select top 100 percent Gather_no,dbo.f_getname(Gather_no) as total_bargain from TGather order by Gather_no