谁能给一个sql function 返回一个table
table是合并好数据的表 例:
表A
ID varId
1 001
1 002
1 003
表B
varId varName
001 name1
002 name2
003 name3 函数返回的表
ID varId varName
1 001,002,003 name1,name2,name3
或者
ID var
1 001_name1,002_name2,003_name3
table是合并好数据的表 例:
表A
ID varId
1 001
1 002
1 003
表B
varId varName
001 name1
002 name2
003 name3 函数返回的表
ID varId varName
1 001,002,003 name1,name2,name3
或者
ID var
1 001_name1,002_name2,003_name3
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
GO
CREATE TABLE A(ID INT,VARID VARCHAR(10))
go
insert A SELECT 1,'001'
UNION ALL SELECT 1,'002'
UNION ALL SELECT 1,'003'
GO
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
GO
CREATE TABLE B(VARID VARCHAR(10),VARNAME VARCHAR(10))
go
insert B SELECT '001','NAME1'
UNION ALL SELECT '002','NAME2'
UNION ALL SELECT '003','NAME3'
GOCREATE FUNCTION dbo.f_tb(@id int)
RETURNS @s table(id int,s varchar(100))
AS
BEGIN
DECLARE @str varchar(8000)
SELECT @str = isnull(@str +',','')+VARID+'_'+VARNAME FROM (select id ,A.VARID VARID , VARNAME FROM A JOIN B ON A.VARID=B.VARID) T where Id=@id
insert @s
values(@id,@str)
return
END
GO SELECT * from dbo.f_tb(1)id s
----------- ----------------------------------------------------------------------------------------------------
1 001_NAME1,002_NAME2,003_NAME3(1 行受影响)go我回你贴了啊
这个就可以直接调用
出来时隔表
insert into cyrix_wxm_A
select 1, '001' union all
select 1 , '002' union all
select 1 , '003'create table cyrix_wxm_B(varId varchar(10), varName varchar(10))
insert into cyrix_wxm_B
select '001' , 'name1' union all
select '002' , 'name2' union all
select '003' , 'name3' create function f_cyrix_wxm( @id int)
returns varchar(1000)
as
begin declare @ret varchar(1000)
set @ret = ''
select @ret=@ret+','+cyrix_wxm_B.varId +'_'+cyrix_wxm_B.varName from cyrix_wxm_A inner join cyrix_wxm_B
on cyrix_wxm_A.varId = cyrix_wxm_B.varId where id =@id return substring(@ret,2,len(@ret))
endselect distinct id,dbo.f_cyrix_wxm(id) from cyrix_wxm_A =====
1 001_NAME1,002_NAME2,003_NAME3