建两个函数吧: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,''))
endcreate 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--没有测试
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,''))
endcreate 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--没有测试
alter function f_tr1(@value varchar(100))
returns varchar(1000)
as
begin
declare @a varchar(1000)
set @a=''
select @a=@a+','+cast(b.FactoryID as varchar(10)) from tbProduct a,tbFactory b,tbPIF c
where c.ProductID=@value and c.FactoryID=b.FactoryID group by b.FactoryID
return(stuff(@a,1,1,''))
end
go
alter 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 c
where c.ProductID=@value and c.FactoryID=b.FactoryID group by b.FactoryName
return(stuff(@a,1,1,''))
end
select distinct 列 from 表
详情看一下函数
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
select a.ProductID,a.ProductName
,FactoryIDList=dbo.f_str(a.ProductID,0)
,FactoryNameList=dbo.f_str(a.ProductID,1)
from tbProduct a,tbPIF b
where a.ProductID=b.ProductID
group by a.ProductID,a.ProductName