请教一个关于关联表查询的sql

liu_lxx 2017-06-23 12:00:35

product表：id,name
attribute表：productId,attributeKey,value

怎么查询同时满足key="key1",value="value1";key="key2",value="value2"的商品id？

...全文

129 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

liu_lxx 2017-06-23

打赏
举报

谢谢回复。我的key1和key2数量不是固定的，这种方式就不适用了吧。

引用 1 楼 sinat_28984567 的回复:

--测试数据
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'

谢谢回复。我的key1和key2数量不是固定的，这种方式就不适用了吧。

二月十六 2017-06-23

打赏
举报

--测试数据

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'

liu_lxx 2017-06-23

打赏
举报

引用 6 楼 sinat_28984567 的回复:

有用，谢谢了

二月十六 2017-06-23

打赏
举报

--测试数据

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 )