SQL查询多对多（四张表）

发布于 2024-12-21 00:01:49 字数 895 浏览 1 评论 0原文

表

产品：id、价格
产品属性：id、产品id、属性id
属性：id、值、attributetypeid
attributetype:id, name

查询

select p.Name, p.Price, att.Id, att.Value, at.Id, at.Name
from Product p
inner join ProductAttribute pa on pa.ProductId = p.Id
inner join Attributes att on att.Id = pa.AttributeId
inner join AttributeType at on att.AttributeTypeId = at.Id

结果

Name              Price    Id    Value     Id      Name
Slr camera           90     1    White      1      Color   
digital camera       98     2    Black      1      Color    
Slr camera           90     4    big        2      Size
digital camera       98     5    medium     2      Size

现在我想通过过滤attributeid即attributeid=1（白色）来检索产品，并且这样做必须仅过滤颜色属性类型而不是尺寸。我的意思是它现在应该检索三行：颜色 - 白色、大小 - 大和大小 - 中的行。

原文

Tables

product: id, price
productattribute:id, productid, attributeid
attributes: id, value, attributetypeid
attributetype:id, name

Query

select p.Name, p.Price, att.Id, att.Value, at.Id, at.Name
from Product p
inner join ProductAttribute pa on pa.ProductId = p.Id
inner join Attributes att on att.Id = pa.AttributeId
inner join AttributeType at on att.AttributeTypeId = at.Id

Result

Name              Price    Id    Value     Id      Name
Slr camera           90     1    White      1      Color   
digital camera       98     2    Black      1      Color    
Slr camera           90     4    big        2      Size
digital camera       98     5    medium     2      Size

Now I want to retrieve products by filtering attributesid ie attributesid=1(which is white) and doing so must filter only the color attributetype not the size. What I mean is it should retrieve three rows now: rows with Color - White, Size - big and Size - medium.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

放赐 2024-12-28 00:01:49

使用 where 子句允许非颜色属性和颜色属性（如果它们是白色）：

where  at.AttributeTypeId <> 1 -- Non-color attribute
       or at.AttributeTypeId = 1 and att.Id = 1 -- White color

Use a where clause to allow non-color attributes and color attributes if they're white:

where  at.AttributeTypeId <> 1 -- Non-color attribute
       or at.AttributeTypeId = 1 and att.Id = 1 -- White color

回复收藏 0 原文

阿楠 2024-12-28 00:01:49

select p.Name,p.Price,att.Id,att.Value,at.Id,at.Name from Product p
inner join ProductAttribute pa on pa.ProductId=p.Id
inner join Attributes att on att.Id=pa.AttributeId
inner join AttributeType at on att.AttributeTypeId=at.Id
where att.id = 1 or (at.AttributeTypeId = 1 and att.Id = 1 );

select p.Name,p.Price,att.Id,att.Value,at.Id,at.Name from Product p
inner join ProductAttribute pa on pa.ProductId=p.Id
inner join Attributes att on att.Id=pa.AttributeId
inner join AttributeType at on att.AttributeTypeId=at.Id
where att.id = 1 or (at.AttributeTypeId = 1 and att.Id = 1 );

回复收藏 0 原文

妄想挽回 2024-12-28 00:01:49

也许下面的查询对您有用：

SELECT
  p.Name,
  p.Price,
  att.Id,
  att.Value,
  at.Id,
  at.Name 
FROM
  Product p, ProductAttribute pa, Attributes att, AttributeType at
WHERE pa.ProductId=p.Id
  AND att.Id=pa.AttributeId 
  AND att.AttributeTypeId=at.Id
  AND (  at.Id <> 1
      OR at.Id = 1 AND att.value = 'White')

May be the query bellow would be useful for you:

SELECT
  p.Name,
  p.Price,
  att.Id,
  att.Value,
  at.Id,
  at.Name 
FROM
  Product p, ProductAttribute pa, Attributes att, AttributeType at
WHERE pa.ProductId=p.Id
  AND att.Id=pa.AttributeId 
  AND att.AttributeTypeId=at.Id
  AND (  at.Id <> 1
      OR at.Id = 1 AND att.value = 'White')

回复收藏 0 原文

~没有更多了~