下面要求的sql如何写
表Category
CategoryId Name
1 c1
2 c2
3 c3
表Product：
ProductId Name
1 p1
2 p2
3 p3
表Relationship
ProductId CategoryId
1 1
1 2
2 1
3 2
表Relationship用于描述产品与分类之间的关系，一个产品可以属于多个分类，一个分类有多个产品，现查询产品表要求返回下列结果，即多了一列，把分类名称串起来，显示到界面。
ProductId Name CategoryName
1 p1 c1,c2
2 p2 c2
想了很久无头绪，Please help。
[解决办法]
应该是这样吧
ProductId Name CategoryName
1 p1 c1,c2
2 p2 c1
[解决办法]
with Category(CategoryId,Name)as(
select 1,'c1' union all
select 2,'c2' union all
select 3,'c3'),
Product(ProductId,Name)as(
select 1,'p1' union all
select 2,'p2' union all
select 3,'p3'),
Relationship(ProductId,CategoryId)as(
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 3,2)
select distinct a.ProductId,a.Name,
CategoryName=stuff((select ','+Category.Name from Product ,Relationship ,Category
where Category.CategoryId=Relationship.CategoryId and Relationship.ProductId=b.ProductId
and Relationship.ProductId=b.ProductId and Product.Name=a.Name for xml path('')),1,1,'')
from Product a,Relationship b,Category c
where a.ProductId=b.ProductId and b.CategoryId=c.CategoryId
select 1,1
union all
select 2,2
union all
select 2,1
union all
select 3,2
go
create function dbo.getCategoryName(@productid int)
returns varchar(200)
as
begin
declare @cname varchar(200) =' '
SET @cname=' '
select @cname = c.name +','+@cname
from relationShip r inner join category c
on r.categoryid = c.categoryid
where r.productid =@productid
return (@cname)
end
go
select p.productid, p.name,dbo.getCategoryName(p.productid) from product p
--或者
select p2.productid, p.name,dbo.getCategoryName(p.productid) from
(select productid from product group by productid) p2 inner join
product p
on p2.productid = p.productid
结果：
productid name
----------- -------------------- --------------------------------------------------------------------------------------------
1 p1 c1,c1,
2 p2 c2,c1,c2,c1,
3 p3 c2,c2,
