类型：转载 责任编辑：asp.net 日期：2007/05/23
热门软件下载：
有三个表：
tbProduct 产品主表
ProductID
ProductName
tbFactory 供应商主表
FactoryID
FactoryName
FactoryAddress
FactoryTell
tbPIF 供应商与产品的对应关系表
PIF_ID
ProductID
FactoryID
PackegeType………………
我现在想一次查询（查tbProduct）就把每个产品的对应的所有供应商编号列出来
还有供应商的相关资料也能列出来，可以用逗号分隔的字符串
例如：
ProductID ProductName FactoryIDList FactoryNameList
1 原子弹 1,2,3 测试，XX，某某
2 氢弹 2,3 XX，某某
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
这个SQL语句怎么写？
不用存储过程能做到吗？
网友回答：
建两个函数吧：
create function f_tr1(@value varchar(100))
returns varchar(1000)
as
begin
declare @a varchar(1000)
set @a=
select @a=@a+,+b.FactoryID from tbProduct a,tbFactory b,tbPIF
where c.ProductID=@value and c.FactoryID=b.FactoryID
return(stuff(@a,1,1,))
end
create function f_tr2(@value varchar(100))
returns varchar(1000)
as
begin
declare @a varchar(1000)
set @a=
select @a=@a+,+b.FactoryName from tbProduct a,tbFactory b,tbPIF
where c.ProductID=@value and c.FactoryID=b.FactoryID
return(stuff(@a,1,1,))
end
--查询：
select ProductID,ProductName,FactoryIDList=dbo.f_tr1(ProductID),
FactoryNameList=dbo.f_tr2(ProductID) from tbProduct group by ProductID,ProductName
--没有测试
create table tbProduct(ProductID varchar(4) ,ProductName varchar(6))
insert into tbProduct
select P001,原子弹 union all
select P002, 氢弹
create table tbFactory(FactoryID varchar(2), FactoryName varchar(4),FactoryAddress varchar(10), FactoryTell varchar(11))
insert into tbFactory
select 1,测试,河北,03181234567 union all
select 2,XX,河北,03181234567 union all
select 3,某某,河北,03181234567 union all
select 2,XX,河北,03181234567 union all
select 3,某某,河北,03181234567
create table tbPIF( PIF_ID int, ProductID varchar(4),FactoryID varchar(1))
insert into tbPIF
select 1,P001,1 union all
select 2,P001,2 union all
select 3,P001,3 union all
select 1,P002,2 union all
select 1,P002,3
--查询
select distinct a.* ,b.FactoryID,c.FactoryName
into #t
from tbProduct a left join tbPIF b on a.ProductID=b.ProductID
inner join tbFactory c on b.FactoryID=c.FactoryID
create table #t0(ProductID varchar(4),ProductName varchar(6),FactoryID varchar(2), FactoryName varchar(4),a varchar(100),b varchar(100) )
insert into #t0
select * ,,from #t
declare @s1 varchar(500),@i varchar(10),@s2 varchar(500)
set @s1=
set @i=0
update #t0
set @s1= case when @i=ProductID then @s1+,+ FactoryID else FactoryID end,
@s2=case when @i=ProductID then @s2+,+ FactoryName else FactoryName end
,@i=ProductID,ProductID=@i,a=@s1,b=@s2
select ProductID=ProductID,ProductName=ProductName
,FactoryIDList=max(a)
,FactoryName=max(b)
from #t0
group by ProductID,ProductName
order by ProductID
drop table tbProduct ,tbFactory,tbPIF,#t,#t0
--一个函数就够了
create function f_str(
@ProductID int,
@type bit
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=
if @type=0
select @r=@r+,+cast(FactoryID as varchar)
from tbPIF where ProductID=@ProductID
group by FactoryID
else
select @r=@r+,+rtrim(a.FactoryName)
from tbFactory a,(
select FactoryID from tbPIF
where ProductID=@ProductID
group by FactoryID
)b where a.FactoryID=b.FactoryID
return(stuff(@r,1,1,))
end
go
--调用实现查询
select a.ProductID,a.ProductName
,FactoryIDList=dbo.f_str(ProductID,0)
,FactoryNameList=dbo.f_str(ProductID,1)
from tbPIF a,tbProduct b
where a.ProductID=b.ProductID
group by a.ProductID,a.ProductName