日期:2014-05-18 浏览次数:20577 次
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO create table tb(SKU varchar(10) , PropertiesKey varchar(20) , PropertiesValue varchar(20)) insert into tb values('11' , '纸张尺寸' , 'A4') insert into tb values('11' , '纸张克数' , '70g') insert into tb values('11' , '纸张数量' , '500张') insert into tb values('22' , '纸张尺寸' , 'A3') insert into tb values('22' , '纸张克数' , '80g') insert into tb values('22' , '纸张数量' , '400张') insert into tb values('33' , '纸张尺寸' , 'B4') insert into tb values('33' , '纸张克数' , '70g') go declare @str varchar(100)='纸张尺寸 = a4 + 纸张克数 = 70g' select * from tb where @str like '%'+PropertiesKey+' = '+PropertiesValue+'%' /* SKU PropertiesKey PropertiesValue ---------- -------------------- -------------------- 11 纸张尺寸 A4 11 纸张克数 70g 33 纸张克数 70g (3 行受影响)
------解决方案--------------------
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO create table tb(SKU varchar(10) , PropertiesKey varchar(20) , PropertiesValue varchar(20)) insert into tb values('11' , '纸张尺寸' , 'A4') insert into tb values('11' , '纸张克数' , '70g') insert into tb values('11' , '纸张数量' , '500张') insert into tb values('22' , '纸张尺寸' , 'A3') insert into tb values('22' , '纸张克数' , '80g') insert into tb values('22' , '纸张数量' , '400张') insert into tb values('33' , '纸张尺寸' , 'B4') insert into tb values('33' , '纸张克数' , '70g') go declare @str1 varchar(100)='纸张尺寸 = a4' declare @str2 varchar(100)='纸张克数 = 70g' select SKU from tb a where @str1 like '%'+PropertiesKey+' = '+PropertiesValue+'%' and exists(select 1 from tb where SKU=a.SKU and @str2 like '%'+PropertiesKey+' = '+PropertiesValue+'%') /* SKU ---------- 11
------解决方案--------------------
select a.sku from tb a inner join tb b on a.sku=b.sku where a.PropertiesKey='纸张尺寸' and a.PropertiesValue='A4' and b.PropertiesKey='纸张克数' and a.PropertiesValue='70克'
------解决方案--------------------
属性是动态的 需要动态拼接 等楼上两位大神来写。