27,581
社区成员




--测试数据
if not object_id(N'Tempdb..#product') is null
drop table #product
Go
Create table #product([id] int,[name] nvarchar(23))
Insert #product
select 1,N'产品1' union all
select 2,N'产品2'
GO
if not object_id(N'Tempdb..#attribute') is null
drop table #attribute
Go
Create table #attribute([productId] int,[attributeKey] nvarchar(24),[value] nvarchar(26))
Insert #attribute
select 1,N'key1',N'value1' union all
select 1,N'key2',N'value2' union all
select 2,N'key1',N'value1'
Go
--测试数据结束
SELECT #product.*
FROM #attribute a
JOIN #attribute b ON b.productId = a.productId
JOIN #product ON a.productId = id
WHERE a.attributeKey = 'key1'
AND a.value = 'value1'
AND b.attributeKey = 'key2'
AND b.value = 'value2'
--测试数据
if not object_id(N'Tempdb..#product') is null
drop table #product
Go
Create table #product([id] int,[name] nvarchar(23))
Insert #product
select 1,N'产品1' union all
select 2,N'产品2'
GO
if not object_id(N'Tempdb..#attribute') is null
drop table #attribute
Go
Create table #attribute([productId] int,[attributeKey] nvarchar(24),[value] nvarchar(26))
Insert #attribute
select 1,N'key1',N'value1' union all
select 1,N'key2',N'value2' union all
select 2,N'key1',N'value1'
Go
--测试数据结束
SELECT #product.*
FROM #product
WHERE EXISTS ( SELECT *
FROM #attribute
WHERE attributeKey = 'key1'
AND value = 'value1'
AND productId = id )
AND EXISTS ( SELECT *
FROM #attribute
WHERE attributeKey = 'key2'
AND value = 'value2'
AND productId = id )