create function f_str(@user_name varchar(50))
returns varchar(500)
as
begin
declare @ret varchar(500)
set @ret = ''
select @ret = @ret + g from c1 where bid=@user_name
set @ret = stuff(@ret,1,1,'')
return @ret
end
goselect b.*,dbo.f_str(bid)
from b1 b
group by bid,aid,d,e
returns varchar(500)
as
begin
declare @ret varchar(500)
set @ret = ''
select @ret = @ret + g from c1 where bid=@user_name
set @ret = stuff(@ret,1,1,'')
return @ret
end
goselect b.*,dbo.f_str(bid)
from b1 b
group by bid,aid,d,e
在c1表建函数create function f_test(@bid nvarchar(3))
returns nvarchar(50)
as
begin
declare @s nvarchar(50)
select @s=isnull(@s+',','')+g from c1 where bid=@bid
return @s
end
go
select
distinct b.bid , b.aid b.d ,[g]=dbo.f_test(g)
from
b1 b
join
c1 c on b.bid=c.bid
where
b.aid='001'
b.bid , b.aid , b.d ,[g]=dbo.f_test(g)
from
b1 b
join
c1 c on b.bid=c.bid
where
b.aid='001'
group by b.bid , b.aid , b.d
insert a1 select '001',1,2,3
insert a1 select '002',4,4,5 create table b1(bid char(3),aid char(3), d char(3),e char(3))insert b1 select '001','001','er','er'
insert b1 select '002','002','rr','rr' create table c1(cid char(3),bid char(3),f varchar(3),g varchar(3))
insert c1 select '001','001','fe','a'
insert c1 select '002','001','f0','b'
insert c1 select '003','001','122','c'
gocreate function f_str(@user_name varchar(50))
returns varchar(500)
as
begin
declare @ret varchar(500)
set @ret = ''
select @ret = @ret + g from c1 where bid=@user_name
-- set @ret = stuff(@ret,1,1,'')
return @ret
end
go
select b.* ,dbo.f_str(bid)
from b1 b
left join a1 a
on a.aid = b.aid
where a.aid = '001'/*
bid aid d e
---- ---- ---- ---- ------------
001 001 er er abc
*/
drop function f_str
drop table a1,b1,c1
returns nvarchar(50)
as
begin
declare @s nvarchar(50)
select @s=isnull(@s,'')+g from c1 where bid=@bid
return @s
end
create function wsp(@bid varchar(10))
returns varchar(200)
as
begin
declare @return varchar(200)
select @return=isnull(@return+',','')+g from c1
return @return
endselect b1.*,dbo.wsp(b1.bid)g from b1,c1 where b1.bid=c1.bid
create or replace function getAllRecord(conditionchildtypeid in nvarchar(18),
return varchar2(500) is myresult varchar(500))
begin
select myresult:=myresult||','||a.describe from MM_SUPPLIERRECORD a
where a.conditionchildtypeid=conditionchildtypeid
return myresult;
end;
end getAllRecord;建成的函数有错误,发现不出哪里有错
create or replace function getAllRecord(conditionchildtypeid in nvarchar(18),
return varchar2(500) is myresult varchar(500))
begin
select myresult:=myresult||','||a.describe from MM_SUPPLIERRECORD a
where a.conditionchildtypeid=conditionchildtypeid
return myresult;
end;
end getAllRecord;