楼上的兄弟,我用了你的测试得不到值,可能是我操作的有问题吧,不过根据两位的提示,我总算搞定了,不过我觉得我写的没有楼上写的效率高吧,但是我用楼上的一时又搞不定,我就先用我自己写的了,有时间再好好的调试一下楼上的吧,谢过两位了。我写的如下,还往指教:
CREATE FUNCTION fn_productname(@id varchar(100))
RETURNS varchar(500)
AS
BEGIN
declare @s varchar(500),@stempstr varchar(500)
set @s=''
set @stempstr=''
while len(@id)>0
begin
if charindex(',',@id)>0
begin
set @stempstr=left(@id,charindex(',',@id)-1)
set @id=right(@id,len(@id)-charindex(',',@id))
end
else
begin
set @stempstr=@id
set @id=''
end
select @s=@s+pname+'、' from product where coid in (@stempstr)
end
return (@s)
END
CREATE FUNCTION fn_productname(@id varchar(100))
RETURNS nvarchar(500)
AS
BEGIN
declare @s nvarchar(500)
set @s=''
select @s=@s+pname+'、' from product where Charindex(','+CoId+',',','+@id+',')>0
return (@s)
END
CREATE FUNCTION fn_productname(@id varchar(100))
RETURNS nvarchar(500)
AS
BEGIN
declare @s nvarchar(500)
set @s=''
select @s=@s+pname+'、' from product where CoID in (@id)
return (@s)
END
但是如果我把函数改为如下就能得到正确的结果,但这样我代入的ID无效啊,我只是这样做一下测试,但我不知道什么原因,如何解决呢。
CREATE FUNCTION fn_productname(@id varchar(100))
RETURNS nvarchar(500)
AS
BEGIN
declare @s nvarchar(500)
set @s=''
select @s=@s+pname+'、' from product where CoID in (1,2)
return (@s)
END
--函數
create function dbo.fn_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[B]+'、' from tb where A=@a ---看實際情況,這裡加“、”
return (@s)
end
create function f_contractstr(@CoID nvarchar(100))
returns nvarchar(2000)
as
begin
declare @s nvarchar(2000)
set @s=''
select @s=@s+','+pname from product where CoID=@CoID
return(stuff(@s,1,1,''))
end
go
---
select coid,dbo.f_contractstr(coid) as pname from product group by coid
--建表
create table tb (A varchar(10),B varchar(1000))
Insert into tb
select '111','AA0000,AA0001,AA0002,'
union all select '111','BB0000,BB0001,BB0002,'
union all select '222','AA0120,AA0102,AA1453,'
union all select '222','BB1212,BB3423,BB2123,'
select * from tb
--函數
create function dbo.fn_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[B] from tb where A=@a
return (@s)
end
--刪除
drop table tb
drop function dbo.fn_b
--結果
select A,B=dbo.fn_b(A) from tb group by A
A B
-------------------------------------------
111 AA0000,AA0001,AA0002,BB0000,BB0001,BB0002,
222 AA0120,AA0102,AA1453,BB1212,BB3423,BB2123,