动态属性SQL语句,该怎么处理
动态属性SQL语句
类似淘宝多条件过滤查询。
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
create table tb(SKU varchar(10) , PropertiesKey varchar(10) , PropertiesValue varchar(10))
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
条件:纸张尺寸 = a4 + 纸张克数 = 70g,查询,这个SQL怎么写,效率高。
------解决方案--------------------
类似淘宝多条件过滤查询。
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
create table tb(SKU varchar(10) , PropertiesKey varchar(10) , PropertiesValue varchar(10))
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
条件:纸张尺寸 = a4 + 纸张克数 = 70g,查询,这个SQL怎么写,效率高。
------解决方案--------------------
- SQL code
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 行受影响)
------解决方案--------------------
- SQL code
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
------解决方案--------------------
- SQL code
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克'
------解决方案--------------------
属性是动态的 需要动态拼接 等楼上两位大神来写。