create table ta([m_id] int,[m_name] nvarchar(10),[m_info] text) insert ta select 1,'test1','test1' union all select 2,'test2' ,'test2'union all select 3,'test3','test3' create table tb([m_id] int,[p_id] int) insert tb select 1,1 union all select 1,2 union all select 1,3 union all select 2,2 union all select 2,3 union all select 3,1 union all select 3,3 create table tc([m_id] int,[p_id] int) insert tc select 1,1 union all select 1,4 union all select 2,2 union all select 3,2 union all select 3,6 create table td([p_id] int,[p_name] nvarchar(10),[p_info] text) insert td select 1,'name1','name1' union all select 2,'name2','name2' union all select 3,'name3','name3' union all select 4,'name4','name4' union all select 5,'name5','name5' union all select 6,'name6','name6' /* m_id m_name p_id_b_str p_id_c_str p_name_b_str p_name_c_str */ go create function f_str(@m_id int) returns varchar(100) as begin declare @ret varchar(100) select @ret = isnull(@ret+',','') + ltrim(p_id) from tb where m_id = @m_id return @ret end go create function f_str1(@m_id int) returns varchar(100) as begin declare @ret varchar(100) select @ret = isnull(@ret+',','') + ltrim(p_id) from tc where m_id = @m_id return @ret end goselect a.m_id,a.m_name,dbo.f_str(a.m_id) as p_id_b_str,dbo.f_str1(a.m_id) as p_id_c_str from ta a drop table ta,tb,tc,td drop function f_str,f_str1/* m_id m_name p_id_b_str p_id_c_str ----------- ---------- -------------- ----------- 1 test1 1,2,3 1,4 2 test2 2,3 2 3 test3 1,3 2,6(所影响的行数为 3 行) */
create table ta([m_id] int,[m_name] nvarchar(10),[m_info] text) insert ta select 1,'test1','test1' union all select 2,'test2' ,'test2'union all select 3,'test3','test3' create table tb([m_id] int,[p_id] int) insert tb select 1,1 union all select 1,2 union all select 1,3 union all select 2,2 union all select 2,3 union all select 3,1 union all select 3,3 create table tc([m_id] int,[p_id] int) insert tc select 1,1 union all select 1,4 union all select 2,2 union all select 3,2 union all select 3,6 create table td([p_id] int,[p_name] nvarchar(10),[p_info] text) insert td select 1,'name1','name1' union all select 2,'name2','name2' union all select 3,'name3','name3' union all select 4,'name4','name4' union all select 5,'name5','name5' union all select 6,'name6','name6' /* m_id m_name p_id_b_str p_id_c_str p_name_b_str p_name_c_str */ go create function f_str(@m_id int) returns varchar(20) as begin declare @ret varchar(100) select @ret = isnull(@ret+',','') + ltrim(p_id) from tb where m_id = @m_id return @ret end go create function f_str1(@m_id int) returns varchar(20) as begin declare @ret varchar(100) select @ret = isnull(@ret+',','') + ltrim(p_id) from tc where m_id = @m_id return @ret end gocreate function f_str2(@s varchar(100)) returns varchar(20) as begin set @s = ','+@s+',' select @s = replace(@s,','+ltrim(p_id)+',',','+p_name+',') from td where charindex(','+ltrim(p_id)+',',','+@s+',') > 0 set @s = left(@s,len(@s) - 1) return right(@s,len(@s) - 1) end goselect a.m_id,a.m_name,dbo.f_str(a.m_id) as p_id_b_str,dbo.f_str1(a.m_id) as p_id_c_str, dbo.f_str2(dbo.f_str(a.m_id)) as p_name_b_str, dbo.f_str2(dbo.f_str1(a.m_id)) as p_name_c_str from ta a drop table ta,tb,tc,td drop function f_str,f_str1,f_str2/* m_id m_name p_id_b_str p_id_c_str p_name_b_str p_name_c_str ----------- ---------- -------------------- -------------------- -------------------- -------------------- 1 test1 1,2,3 1,4 name1,name2,name3 name1,name4 2 test2 2,3 2 name2,name3 name2 3 test3 1,3 2,6 name1,name3 name2,name6(所影响的行数为 3 行)*/
insert ta
select 1,'test1','test1' union all
select 2,'test2' ,'test2'union all
select 3,'test3','test3'
create table tb([m_id] int,[p_id] int)
insert tb
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,2 union all
select 2,3 union all
select 3,1 union all
select 3,3
create table tc([m_id] int,[p_id] int)
insert tc
select 1,1 union all
select 1,4 union all
select 2,2 union all
select 3,2 union all
select 3,6
create table td([p_id] int,[p_name] nvarchar(10),[p_info] text)
insert td
select 1,'name1','name1' union all
select 2,'name2','name2' union all
select 3,'name3','name3' union all
select 4,'name4','name4' union all
select 5,'name5','name5' union all
select 6,'name6','name6'
/*
m_id m_name p_id_b_str p_id_c_str p_name_b_str p_name_c_str
*/
go
create function f_str(@m_id int)
returns varchar(100)
as
begin
declare @ret varchar(100)
select @ret = isnull(@ret+',','') + ltrim(p_id) from tb where m_id = @m_id
return @ret
end
go
create function f_str1(@m_id int)
returns varchar(100)
as
begin
declare @ret varchar(100)
select @ret = isnull(@ret+',','') + ltrim(p_id) from tc where m_id = @m_id
return @ret
end
goselect a.m_id,a.m_name,dbo.f_str(a.m_id) as p_id_b_str,dbo.f_str1(a.m_id) as p_id_c_str
from ta a
drop table ta,tb,tc,td
drop function f_str,f_str1/*
m_id m_name p_id_b_str p_id_c_str
----------- ---------- -------------- -----------
1 test1 1,2,3 1,4
2 test2 2,3 2
3 test3 1,3 2,6(所影响的行数为 3 行)
*/
insert ta
select 1,'test1','test1' union all
select 2,'test2' ,'test2'union all
select 3,'test3','test3'
create table tb([m_id] int,[p_id] int)
insert tb
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 2,2 union all
select 2,3 union all
select 3,1 union all
select 3,3
create table tc([m_id] int,[p_id] int)
insert tc
select 1,1 union all
select 1,4 union all
select 2,2 union all
select 3,2 union all
select 3,6
create table td([p_id] int,[p_name] nvarchar(10),[p_info] text)
insert td
select 1,'name1','name1' union all
select 2,'name2','name2' union all
select 3,'name3','name3' union all
select 4,'name4','name4' union all
select 5,'name5','name5' union all
select 6,'name6','name6'
/*
m_id m_name p_id_b_str p_id_c_str p_name_b_str p_name_c_str
*/
go
create function f_str(@m_id int)
returns varchar(20)
as
begin
declare @ret varchar(100)
select @ret = isnull(@ret+',','') + ltrim(p_id) from tb where m_id = @m_id
return @ret
end
go
create function f_str1(@m_id int)
returns varchar(20)
as
begin
declare @ret varchar(100)
select @ret = isnull(@ret+',','') + ltrim(p_id) from tc where m_id = @m_id
return @ret
end
gocreate function f_str2(@s varchar(100))
returns varchar(20)
as
begin
set @s = ','+@s+','
select @s = replace(@s,','+ltrim(p_id)+',',','+p_name+',') from td
where charindex(','+ltrim(p_id)+',',','+@s+',') > 0
set @s = left(@s,len(@s) - 1)
return right(@s,len(@s) - 1)
end
goselect a.m_id,a.m_name,dbo.f_str(a.m_id) as p_id_b_str,dbo.f_str1(a.m_id) as p_id_c_str,
dbo.f_str2(dbo.f_str(a.m_id)) as p_name_b_str,
dbo.f_str2(dbo.f_str1(a.m_id)) as p_name_c_str
from ta a
drop table ta,tb,tc,td
drop function f_str,f_str1,f_str2/*
m_id m_name p_id_b_str p_id_c_str p_name_b_str p_name_c_str
----------- ---------- -------------------- -------------------- -------------------- --------------------
1 test1 1,2,3 1,4 name1,name2,name3 name1,name4
2 test2 2,3 2 name2,name3 name2
3 test3 1,3 2,6 name1,name3 name2,name6(所影响的行数为 3 行)*/