create function f_union(@id int)
returns varchar(100)
as
begin
declare @sql varchar(700)
set @sql=''
select @sql=@sql+','+test from table2 where productid=@id
return(stuff(@sql,1,1,''))
end
goselect a.productname,dbo.f_union(b.productid)
from table1 a,table2 b
where a.productid=b.productid
group by b.productid
returns varchar(100)
as
begin
declare @sql varchar(700)
set @sql=''
select @sql=@sql+','+test from table2 where productid=@id
return(stuff(@sql,1,1,''))
end
goselect a.productname,dbo.f_union(b.productid)
from table1 a,table2 b
where a.productid=b.productid
group by b.productid
create table table1(productid int,productname varchar(10))
Go
insert table1 select 1,'产品A'
insert table1 select 2,'产品B'
Go
create table table2(orderid int,productid int,customer varchar(10))
Go
insert table2 select 1,1,'顾客A'
insert table2 select 2,1,'顾客B'
insert table2 select 3,1,'顾客C'
insert table2 select 4,2,'顾客D'
insert table2 select 5,2,'顾客E'--创建函数---------------------------------------------alter function dbo.fn_GetCustomers
(@productid int)
returns varchar(100)
as
begin
Declare @v_customers varchar(1000)
set @v_customers=''
select @v_customers=@v_customers+customer+','
from table2
where productid=@productid
return(left(@v_customers,len(@v_customers)-1))
end
Go--查询--------------------------------------------
select productname,dbo.fn_GetCustomers(productid) as customers
from table1
--结果---------------------------------------------
productname
----------- ----------------------------------------------------------------------------------------------------
产品A 顾客A,顾客B,顾客C
产品B 顾客D,顾客E(所影响的行数为 2 行)
return(stuff(@sql,1,1,''))和select @v_customers=@v_customers+customer+','
from table2
where productid=@productid
return(left(@v_customers,len(@v_customers)-1))这两段语句的意思?
2 是从右边去掉第一个字符
方法都都差不多 both are OK