动态表的操作

Question

本人最近负责一个项目，这个项目其中商品有如下几类（注：里面是类别中的商品）电脑HP（笔记本、台式机），组装机（鼠标、键盘、音响、CUP、内存、显卡……） ，外设，投影机 ，音视频，监控等  问题：这些商品大多没有相同的参数，难道需要每个设备建立一张表吗，这样子就有30多张表之多，维护起来太麻烦了本人是这样子设计的：
类别表：CategoryId,Name,ParentId...（无限级分类）
产品表：ProductId,ProductName,CategoryId...（产品属于某个分类）
属性表：PropertyId,PropertyName,PropertySize...（属性的信息）
关联表：ID ProductId PropertyId PropertyValue...(产品和属性的关联表)能不能请各位给出操作动态表比较好的方式或SQL语句参考
如：
1、查询出鼠标列表
2、添加、修改商品信息我觉得如果能够确定一张表，动态生成视图应该能够提高性能

Answer 1

最好给出完整的表结构,测试数据,计算方法和正确结果.发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

Answer 2

好的，现给出表结构以及测试数据-- 类别表
create table Category
(
Id int identity primary key, -- 类别编号
Name varchar(20) not null, -- 类别名称
ParentId int not null -- 父级Id
)
go
insert into Category values('HP电脑',0)
insert into Category values('组装机',0)
insert into Category values('鼠标',2)
go-- 属性表
create table Property
(
Id int identity primary key, -- 属性Id
Name varchar(20) not null, -- 属性名称
Type varchar(20) not null, -- 属性类型
Size int not null, -- 属性大小
CategoryId int not null -- 所属分类
)
go
insert into [Property] values('鼠标引擎','string',20,3)
insert into [Property] values('按键数','int',4,3)
insert into [Property] values('鼠标接口','string',10,3)
go-- 产品表
create table Product
(
Id int identity primary key, -- 产品编号
Name varchar(20) not null, -- 产品名称
CategoryId int not null -- 所属类别
)
go
insert into Product values('罗技M215无线鼠标',3)
insert into Product values('雷柏V2游戏鼠标',3)
go-- 产品属性关联表
create table ProductProperty
(
Id int identity primary key, -- 关联Id
ProductId int not null, -- 产品Id
PropertyId int not null, -- 属性Id
PropertyValue varchar(2000) -- 属性值
)
go
insert into ProductProperty values(1,1,'光电')
insert into ProductProperty values(1,2,'3')
insert into ProductProperty values(1,3,'USB')
insert into ProductProperty values(2,1,'激光')
insert into ProductProperty values(2,2,'9')
insert into ProductProperty values(2,3,'USB')
goselect * from Category
select * from Property
select * from Product
select * from ProductProperty
go
我到得到一些结果
比如得到鼠标类型的产品列表鼠标名称          鼠标引擎 按键数    鼠标接口
-----------------------------------------------
罗技M215无线鼠标 光电 3 USB
雷柏V2游戏鼠标 激光 9 USB

Answer 3

declare @typeid int --类型ID
set @typeid=3
declare @typename varchar(30)
select @typename=name from Category where Id=@typeid
declare @fieldlist varchar(100) --字段列
set @fieldlist=','
select @fieldlist=@fieldlist+isnull(name,'')+',' from Property where CategoryId=@typeid
if left(@fieldlist,1)=',' set @fieldlist=right(@fieldlist,LEN(@fieldlist)-1)
if right(@fieldlist,1)=',' set @fieldlist=left(@fieldlist,LEN(@fieldlist)-1)
if isnull(ltrim(@fieldlist),'')='' set @fieldlist='无属性'
declare @sql varchar(1000) --sql语句
set @sql='
select * from (
select
b.Name '+@typename+'名称,c.Name tn,d.PropertyValue
from Category a
inner join Product b on b.CategoryId=a.Id
inner join Property c on c.CategoryId=a.Id
inner join ProductProperty d on d.ProductId=b.Id and d.PropertyId=c.Id
where a.Id='+CAST(@typeid as varchar(10))+'
) a
pivot 
(
max(PropertyValue) for tn in ('+@fieldlist+')
) as pvt
'
exec(@sql)
go

Answer 4

6楼，这个SQL2000也可以运行吧，性能怎么样

Answer 5

DECLARE @s VARCHAR(8000)
SELECT @s=ISNULL(@s,'')+',MAX(CASE c.Name WHEN '''+Name+''' THEN PropertyValue ELSE '''' END)['+Name+']'
FROM Property
EXEC('
SELECT b.Name 鼠标名称'+@s+'
FROM Category a
INNER JOIN Product b
ON  b.CategoryId = a.Id
INNER JOIN PROPERTY c
ON  c.CategoryId = a.Id
INNER JOIN ProductProperty d
ON  d.ProductId = b.Id
AND d.PropertyId = c.Id
WHERE a.Id = 3
GROUP BY b.Name
')
/*
鼠标名称 鼠标引擎 按键数 鼠标接口
雷柏V2游戏鼠标 激光 9 USB
罗技M215无线鼠标 光电 3 USB
*/