设计属性不固定的表

相关贴子：点击打开链接

--1. 材料表
--2. 产品表
--3. 产品材料中间表
--4. 属性表
--5. 材料属性中间表
USE tempdb
GO
IF OBJECT_ID('material_data') IS NOT NULL DROP TABLE material_data
IF OBJECT_ID('product_data') IS NOT NULL DROP TABLE product_data
IF OBJECT_ID('product_material_mid') IS NOT NULL DROP TABLE product_material_mid
IF OBJECT_ID('property_data') IS NOT NULL DROP TABLE property_data
IF OBJECT_ID('material_property_mid') IS NOT NULL DROP TABLE material_property_mid
GO
CREATE TABLE material_data (
	materialId INT IDENTITY(1,1) PRIMARY KEY,
	materialName NVARCHAR(20)	
)
CREATE TABLE product_data(
	productId  INT IDENTITY(1,1) PRIMARY KEY,
	productName NVARCHAR(20)	
)
CREATE TABLE product_material_mid(
	id  INT IDENTITY(1,1) PRIMARY KEY,
	productId INT NOT NULL,
	materialId INT NOT NULL
)
CREATE UNIQUE INDEX UQ_product_material_mid ON product_material_mid(productId,materialId);
CREATE TABLE property_data(
	propertyId INT IDENTITY(1,1) PRIMARY KEY,
	propertyName NVARCHAR(20)
)
CREATE TABLE material_property_mid(
	id  INT IDENTITY(1,1) PRIMARY KEY,
	materialId INT NOT NULL,
	propertyId INT NOT NULL,
	propertyValue NVARCHAR(200)
)
CREATE UNIQUE INDEX UQ_product_material_mid ON material_property_mid(materialId,propertyId);

INSERT INTO material_data(materialName) VALUES('A')
INSERT INTO material_data(materialName) VALUES('B')
INSERT INTO product_data(productName) VALUES('高档家具')
INSERT INTO product_data(productName) VALUES('中档家具')

INSERT INTO product_material_mid(productId,materialId) VALUES(1,1)
INSERT INTO product_material_mid(productId,materialId) VALUES(2,1)
INSERT INTO product_material_mid(productId,materialId) VALUES(2,2)

--A.P
INSERT INTO property_data VALUES('名称');
INSERT INTO property_data VALUES('等级');
INSERT INTO property_data VALUES('种类');
INSERT INTO property_data VALUES('颜色');
INSERT INTO property_data VALUES('颜色号');
--B.P
INSERT INTO property_data VALUES('规格');
--
INSERT INTO material_property_mid VALUES(1,1,'实木材质')
INSERT INTO material_property_mid VALUES(1,2,'2')
INSERT INTO material_property_mid VALUES(1,3,'实木')
INSERT INTO material_property_mid VALUES(1,4,'胡桃色')
INSERT INTO material_property_mid VALUES(1,5,'8')
INSERT INTO material_property_mid VALUES(2,1,'中纤板材质')
INSERT INTO material_property_mid VALUES(2,6,'8"*4"')

SELECT p.productName,mat.materialName
,(
	SELECT a.propertyName+': '+b.propertyValue+'; '
	  FROM property_data a INNER JOIN material_property_mid AS b 
		ON a.propertyId=b.propertyId AND b.materialId=mat.materialId  
	FOR XML PATH('')
) AS properties
FROM product_data AS P 
	INNER JOIN product_material_mid AS mid ON p.productId=mid.productId 
	INNER JOIN material_data AS mat ON mid.materialId=mat.materialId
/*
productName	materialName	properties
高档家具	A	            名称: 实木材质; 等级: 2; 种类: 实木; 颜色: 胡桃色; 颜色号: 8; 
中档家具	A	            名称: 实木材质; 等级: 2; 种类: 实木; 颜色: 胡桃色; 颜色号: 8; 
中档家具	B	            名称: 中纤板材质; 规格: 8"*4"; 
*/