高难度，阿里巴巴行业分类的附加属性查询的问题

Question

表内容：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Product]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SysClassAttribute]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SysClassAttribute]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SysClassAttributeValue]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SysClassAttributeValue]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysclass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sysclass]
GOCREATE TABLE [dbo].[Product] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Productname] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[SysclassID] [int] NULL 
) ON [PRIMARY]
GO
/*productname:产品名称；sysclassID:系统行业分类的关联ID号*/ CREATE TABLE [dbo].[SysClassAttribute] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[EN_AttributeName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[GB_AttributeName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[SysclassID] [int] NULL 
) ON [PRIMARY]
GO
/*GB_AttributeName:产品附加属性的中文名称；sysclassID:系统行业分类的关联ID号*/ CREATE TABLE [dbo].[SysClassAttributeValue] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductID] [int] NULL ,
[SysClassAttributeID] [int] NULL ,
[SysClassAttributeStr] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
/*productID:产品关联ID号；SysClassAttributeID:产品附加属性表里的关联ID号；SysClassAttributeStr   产品具体拥有的附加属性的值*/ CREATE TABLE [dbo].[sysclass] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Classname] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[upID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO
/*classname:行业分类名称；UPID:系统行业分类的关联上线ID号，如果为0表示为顶级分类*/ insert into product (productname,sysclassID)values('花朵童装',5)
insert into product (productname,sysclassID)values('红孩子童装',5)
insert into product (productname,sysclassID)values('红旗电机',3)
insert into product (productname,sysclassID)values('抽水电机',3)insert into SYSCLASS (classname,upid)values('抽水电机',0)
insert into SYSCLASS (classname,upid)values('',0)
insert into SYSCLASS (classname,upid)values('电机',1)
insert into SYSCLASS (classname,upid)values('服装',0)
insert into SYSCLASS (classname,upid)values('童装',4)insert into SysClassAttribute (EN_AttributeName,GB_AttributeName,SysclassID)values('','面料',5)
insert into SysClassAttribute (EN_AttributeName,GB_AttributeName,SysclassID)values('','size',5)
insert into SysClassAttribute (EN_AttributeName,GB_AttributeName,SysclassID)values('','季节',5)
insert into SysClassAttribute (EN_AttributeName,GB_AttributeName,SysclassID)values('','转速',3)
insert into SysClassAttribute (EN_AttributeName,GB_AttributeName,SysclassID)values('','启动方式',3)
insert into SysClassAttribute (EN_AttributeName,GB_AttributeName,SysclassID)values('','线圈电阻',3)
insert into SysClassAttribute (EN_AttributeName,GB_AttributeName,SysclassID)values('','电功率',3)
insert into SysClassAttribute (EN_AttributeName,GB_AttributeName,SysclassID)values('','输入电压',3)insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(3,4,'500')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(3,5,'直流')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(3,6,'50')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(3,7,'600W')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(3,8,'60V')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(4,4,'500')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(4,5,'直流')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(4,8,'80V')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(1,1,'20%棉       50%毛')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(1,2,'xx')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(1,3,'冬')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(2,1,'20%棉       75%毛')
insert into SysClassAttributeValue (ProductID,SysClassAttributeID,SysClassAttributeStr)values(2,3,'冬')
go
我希望通过一句复合查询得到以下的结果：
当用户选择童装里的：花朵童装，红孩子童装时： 
的对比结果是： 
id      产品名                分类            价格           面料                                size                      季节   1       花朵童装               童装           95元           20%棉       50%毛                   xx                       冬   2       红孩子童装             童装           50元           20%棉       75%毛                                                冬   
当用户选择电机里的：红旗电机，抽水电机时： 
的对比结果是： ID     产品名           分类           价格           转速      启动方式          线圈电阻率       电功率        输入电压3      红旗电机         电机           150元          500       直流                  50          600W         60V   4      抽水电机         电机           120元          550       直流                                                80V 请高手点拨! 这个功能是阿里巴巴的B2B系统里新版启用的一个功能，对于做好全行业B2B网站里的垂直行业细分功能与属性有很好的作用，这个附加属性做得好的话，可以取代垂直行业网站!

Answer 1

搞了半个小时,四个表,把人都转晕了.
价格在四个表中都没有.
面料,SIZE取不同的字段,但结果却在一个字段中出现,且size后还跟个无聊的XX.楼主可是害人不浅.

Answer 2

alter proc GatProduct
(
@sysclassid varchar(5) --传入类别id参数
)
as
  declare @cursorsql varchar(500)
 
      DECLARE @sql VARCHAR(8000),@title varchar(200),@colid int
   set @sql = 'select productid,max(productname),classname,'
   set @cursorsql ='DECLARE titles_cursor2 CURSOR FOR SELECT id,GB_AttributeName1 FROM dbo.SysClassAttribute WHERE SysclassID = '+@sysclassid
   exec (@cursorsql)
   exec('OPEN titles_cursor2')
   FETCH NEXT FROM titles_cursor2 INTO @colid,@title   WHILE @@FETCH_STATUS = 0
   BEGIN
SELECT @sql = @sql +'['+ @title+ ']= MAX(CASE WHEN sysclassattributeid =  '+CAST(@colid AS VARCHAR)+ ' 
                                                             THEN SysClassAttributeStr END),'       FETCH NEXT FROM titles_cursor2 INTO @colid,@title
   
   END
   CLOSE titles_cursor2
   
    set  @sql = substring(@sql,1,len(@sql)-1)
    SELECT @sql = @sql +  ' from (select a.*,b.classname,c.productid,c.sysclassattributeid,c.sysclassattributestr  
    from  Product a ,sysclass b,SysClassAttributeValue c
    where a.sysclassid = '+@sysclassid+' and a.SYSclassID=b.ID and a.id = c.productid ) h
    group by productid,classname'
    exec( @sql)   

Answer 3

上楼主当了,其实就是个行转列,说的好神奇
数据量大的话,写循环好了.
--select * from product
--select * from sysclassattribute
--select * from sysclassattributevalue
--select * from sysclassDECLARE @pids VARCHAR(1000),@cnt INT,@gid INT
SELECT @pids='1,2'
SELECT @cnt=COUNT(*),@gid=SysClassID FROM product WHERE CHARINDEX(','+RTRIM(id)+',',','+@pids+',')>0 GROUP BY SysClassID
IF @@rowcount!=1 or @cnt!=(LEN(@pids)-LEN(REPLACE(@pids,',',''))+1)
PRINT '几个产品不是同一种类，或不存在指定产品，无法比较'
ELSE
BEGIN
DECLARE @sql VARCHAR(8000)
/*SELECT p.id,p.ProductName,sa.gb_AttributeName,sv.ProductID,sv.SysClassAttributeStr
FROM sysclassattribute sa
INNER JOIN SysClassAttributeValue sv
on sa.id=sv.sysclassattributeid
INNER JOIN product p
on p.id=sv.productid
WHERE CHARINDEX(','+RTRIM(p.id)+',',','+@pids+',')>0*/

SELECT @sql='SELECT p.ID,ProductName'
SELECT @sql=@sql  + ',' + gb_AttributeName + '=MAX(CASE gb_AttributeName WHEN ''' + gb_AttributeName + ''' THEN SysClassAttributeStr ELSE NULL END)' 
FROM SysClassAttribute WHERE SysClassID=@gid
GROUP BY GB_AttributeName

/*sysclassattribute sa
INNER JOIN SysClass sc
on sa.sysclassid=sc.id
INNER JOIN product p
on p.sysclassid=sc.id
WHERE CHARINDEX(','+RTRIM(p.id)+',',','+@pids+',')>0
GROUP BY p.id,p.Productname,gb_AttributeName*/
SELECT @sql=@sql + ' FROM sysclassattribute sa
INNER JOIN SysClassAttributeValue sv
on sa.id=sv.sysclassattributeid
INNER JOIN product p
on p.id=sv.productid
WHERE CHARINDEX('',''+RTRIM(p.id)+'','','','+@pids+','')>0 
GROUP BY p.id,productName' EXEC (@sql)
END
/*
ID          ProductName  size     季节     面料    
----------- -----------------------------------------
1           花朵童装     xx        冬      20%棉       50%毛
2           红孩子童装   NULL      冬      20%棉       75%毛*/