动态属性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 行受影响)

条件1：纸张尺寸 = a4 
条件2： 纸张克数 = 70g

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克'