首页 新闻 搜索 专区 学院

这个SQL查询这么搞?

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

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

问题补充:

具有所有这些属性

jh_x的主页 jh_x | 初学一级 | 园豆:15
提问于:2011-10-12 16:44
< >
分享
所有回答(13)
0

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
0

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

--同时具有的属性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
1

--用临时表合并处理一下关系表,再查询
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
0

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
0
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
0

查询具有所有这些属性   是所有的属性么?如果是:

 

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
0

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
0

一句话搞定。
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 | 园豆:1975 (小虾三级) | 2011-10-13 15:08
0

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

 

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)

)

OneStranger | 园豆:14 (初学一级) | 2011-10-17 00:06
0

本人有一个简单方案,如果保证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
1

查询包含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
0

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
0

打得火热呀。

到我博客上看。

.NET快速开发框架 | 园豆:914 (小虾三级) | 2011-10-21 11:30
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册