这个SQL查询这么搞？

悬赏园豆：200 [已关闭问题] 关闭于 2013-01-31 11:42

有三张表，表Product有字段ID，表Attribute有字段ID，表Product_Attribute_Mapping有字段ProductId、AttributeId，现在我要根据多个AttributeId查出Product，就是查询具有所有这些属性的产品，怎么搞？

数据库 SQL Server

问题补充：

具有所有这些属性

jh_x | 初学一级 | 园豆：15
提问于：2011-10-12 16:44

< >

所有回答(13)

select * from product where id on (select productid from mapping where attributeid = id1 or attributeid = id2 or attributeid = id3))

或者取出or，用循环循环出id1，id2，id3这些值粉刺获得

感觉这样可以才是，数据库有点问题，暂时打不开无法验证...

ERS | 园豆：728 (小虾三级) | 2011-10-12 16:53

@jh_x:

SELECT a.productID FROM ((SELECT * FROM mapping WHERE attributeid = id1) AS a
inner JOIN (SELECT * FROM mapping WHERE attributeid = id2) AS b ON a.productid = b.productid)

使用多表联查，id1，id2是输入属性，如果还有的话将此查询结果当做一张表再次加一个inner JOIN (SELECT * FROM mapping WHERE attributeid = id3)，依次这样，闲麻烦你就使用while循环，将inner join左右的查询包装成一个表变量，根据属性数目确定循环次数

我知道这样挺麻烦的，继续想新方法吧，暂且用用

支持(0) 反对(0) ERS | 园豆：728 (小虾三级) | 2011-10-12 17:44

具有所有这些属性，“与”判断以下就可以了吧

--同时具有的属性id   aid1,aid2,aid3
SELECT * FROM Product WHERE ID IN
(
SELECT ProductId FROM Product_Attribute_Mapping 
WHERE AttributeId =aid1 AND AttributeId =aid2 AttributeId =aid3
)

人来人往 | 园豆：255 (菜鸟二级) | 2011-10-12 17:54

--用临时表合并处理一下关系表,再查询
SELECT ProductID,AttributeID=CAST(AttributeID as varchar(100)) INTO #t FROM Product_Attribute_Mapping ORDER BY ProductID,AttributeID
DECLARE @ProductID varchar(10),@AttributeID varchar(100)UPDATE #t SET @AttributeID=CASE WHEN @ProductID=ProductID THEN @AttributeID+','+AttributeID ELSE AttributeID END, @ProductID=ProductID, AttributeID=@AttributeID
SELECT * FROM #t--得到最终结果
SELECT ProductID FROM #t Where AttributeID = @AttributeID1 + ',' + @AttributeID2 + ',' + @AttributeID3
--删除测试
DROP TABLE #t
GO

--下面是笨方法

--

一个属性
select
ProductID
from Product_Attribute_Mapping
where AttributeID = @AttributeID1
--两个属性
select
ProductID
from Product_Attribute_Mapping
where AttributeID = @AttributeID2 and ProductID in
( select
ProductID
from Product_Attribute_Mapping
where AttributeID = @AttributeID1)
--三个属性
select
ProductID
from Product_Attribute_Mapping
where AttributeID = @AttributeID3 and ProductID in
(
select
ProductID
from Product_Attribute_Mapping
where AttributeID = @AttributeID2 and ProductID in
(
select
ProductID
from Product_Attribute_Mapping
where AttributeID = @AttributeID1
)
)
--程序循环拼接一下就可以了

彭汉生 | 园豆：1233 (小虾三级) | 2011-10-12 18:10

select * from product where productid in(
select productid from (select distinct productid,attributeid from Product_Attribute_Mapping) as a
where attributeid=id1 or attributeid=id2 or...or attributeid=idn group by productid having count(*)=n

);

ciwen91 | 园豆：205 (菜鸟二级) | 2011-10-12 21:30

SELECT [T4].[ID], [T4].[PRODUCTNAME]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [T3].[ID]) AS [ROW_NUMBER], [T3].[ID], [T3].[PRODUCTNAME]
FROM (
SELECT DISTINCT [T0].[ID], [T0].[PRODUCTNAME]
FROM [DBO].[Product] AS [T0], [DBO].[Product_Attribute_Mapping] AS [T1]
WHERE ([T1].[attributeid] IN (@AttributeID1, @AttributeID2)) AND (((
SELECT COUNT(*)
FROM [DBO].[Product_Attribute_Mapping] AS [T2]
WHERE ([T2].[TEAMID] = [T0].[ID]) AND ([T2].[attributeid] IN (@AttributeID1, @AttributeID2)) AND ([T2].[productid] = [T0].[ID])
)) = @AttributeLength) AND ([T1].[productid] = [T0].[ID])
) AS [T3]
) AS [T4]
WHERE [T4].[ROW_NUMBER] BETWEEN 0 + 1 AND 0 + 100
ORDER BY [T4].[ROW_NUMBER]

jh_x | 园豆：15 (初学一级) | 2011-10-13 09:11

查询具有所有这些属性是所有的属性么？如果是：

select * from Product where ID in (select Productid from (
select distinct Productid,count(*) t1,(select count(*) from Attribute) t2
from Product_Attribute_Mapping
group by Productid) t where t.t1=t.t2)

John29 | 园豆：825 (小虾三级) | 2011-10-13 12:13

SELECT * FROM PRODUCT A WHERE A.ID IN
(
SELECT PRODUCTID FROM Product_Attribute_Mapping
WHERE AttributeId IN (1,2,n)--多个属性ID
)

liangyammu | 园豆：4 (初学一级) | 2011-10-13 14:25

一句话搞定。
Product表：

Attributes表：

Product_Attribute_Mapping表：

SELECT p.* FROM Product p,(
SELECT PAM.Product_id,COUNT(PAM.attribute_id)q FROM Attributes A,Product_Attribute_Mapping pam
WHERE A.id=pam.attribute_id
GROUP BY PAM.Product_id HAVING(COUNT(PAM.attribute_id))=(SELECT COUNT(0) FROM Attributes))b
WHERE p.Id=b.Product_id

结果：

webaspx | 园豆：1973 (小虾三级) | 2011-10-13 15:08

我建议以下方案，因为产品属性是用户动态选择的，并非是属性表的所有记录

create table #Product( ProdID int ,ProdName nvarchar(100))

create table #Prod_Attribute( RowID int, ProdID int ,AttributeID int)

create table #Attribute(AttributeID int,AttributeName nvarchar(100))

insert #Product select 1,'product1' union all select 2,'Product2'

insert #Attribute select 1,'Attribute1' union all select 2,'Attribute2' union all select 3,'Attribute3'

insert #Prod_Attribute select 1,1,1 union all select 2,1,2 union all select 3,1,3 union all select 4,2,1 union all select 5,2,2 union all select 6,2,3 union all select 7,2,4

declare @condition varchar(10)

set @condition='2,3'

select AttributeID ,AttributeName into #RealTempTBL from #Attribute where CHARINDEX (rtrim(CAST(AttributeID AS varchar(10))) ,@condition )>0

declare @AttributeCount int set @AttributeCount = (select COUNT (AttributeID) from #RealTempTBL) set @AttributeCount =ISNULL (@AttributeCount,0) select * from #Product where ProdID in ( select ProdID from #Prod_Attribute temp1 inner join #RealTempTBL as temp2 on temp1.AttributeID=temp2.AttributeID group by ProdID HAVING (COUNT(temp1.AttributeID )=@AttributeCount)

)

我是你的小可爱 | 园豆：14 (初学一级) | 2011-10-17 00:06

本人有一个简单方案，如果保证Product_Attribute_Mapping这两个字段上有唯一索引，以下查询语句就可以了。如果不能保证没有重复项，查询需要做更复杂一点。

SELECT prod.ID
FROM [TEST].[dbo].[Attribute] attr,[TEST].[dbo].[Product] prod,[TEST].[dbo].[Product_Attribute_Mapping] map
where attr.ID=map.AttributeId and prod.ID=map.ProductId and attr.ID in ('A1','A2','A3')
group by prod.ID
having COUNT(*)=3
order by prod.ID

chenqin | 园豆：210 (菜鸟二级) | 2011-10-18 13:58

查询包含N个属性 a1,a2,a3,a4.......aN

select * from Product p
where p.ID in(
  select m.ProductId from Product_Attribute_Mapping m
  where m.AttributeId in(a1,a2,a3,....,aN)
  group by m.ProductId
  having count(distinct m.AttributeId)=N
)

71岁的单车 | 园豆：205 (菜鸟二级) | 2011-10-19 10:34

SELECT * FROM Product AS p
WHERE EXISTS
(
   SELECT * FROM Product_Attribute_Mapping AS m
   WHERE EXISTS
   (
       SELECT * FROM Attribute AS a
       WHERE m.attributeId = a.id
   ) AND m.productId = p.id
)

EthanWang | 园豆：107 (初学一级) | 2011-10-19 14:07

打得火热呀。

到我博客上看。

.NET快速开发框架 | 园豆：946 (小虾三级) | 2011-10-21 11:30

清除回答草稿

您需要登录以后才能回答，未注册用户请先注册。

欢迎，请先 登录 或者 注册 。

这个SQL查询这么搞？

欢迎，请先登录或者注册。