使用 MySQL 来 SELECT 类似于 Amazon SimpleDB 的属性

发布于 2024-11-27 03:19:25 字数 1742 浏览 1 评论 0原文

抱歉，我想不出更好的方法来命名此内容。在 Amazon 的 SimpleDB 中，一个项目可以在同一列中具有多个值，因此可以仅选择那些具有所查找的所有属性的项目。

在 MySQL 中，假设下表（“Photo_Attributes”）包含另一个表（“Photos”）中包含的照片的无限数量的属性，并且这两个表通过 Item_Number 连接。

并且，假设我想找到一顶颜色为红色且尺寸为中等的帽子，在本例中为 ITEM_ID“ABC”而不是“OPQ”。

+-----+----------+--------+-----------+-------+  
| ID  | Item_ID  | Object | Attribute | Value |  
+-----+----------+--------+-----------+-------+  
|  1  |  ABC     | Hat    | Color     | Red   |  
+-----+----------+--------+-----------+-------+  
|  2  |  FGH     | Pants  | Color     | Blue  |  
+-----+----------+--------+-----------+-------+  
|  3  |  FGH     | Pants  | Size      | Large |  
+-----+----------+--------+-----------+-------+  
|  4  |  LMN     | Shirt  | Color     | Red   |  
+-----+----------+--------+-----------+-------+  
|  5  |  ABC     | Hat    | Size      | Med   |  
+-----+----------+--------+-----------+-------+  
|  6  |  LMN     | Shirt  | Size      | Med   |  
+-----+----------+--------+-----------+-------+  
|  7  |  OPQ     | Hat    | Color     | White |  
+-----+----------+--------+-----------+-------+  
|  8  |  OPQ     | Hat    | Size      | Med   |  
+-----+----------+--------+-----------+-------+

以下查询不会产生任何结果，因为每一行仅包含一个属性和一个值。

从 Photo_Attributes 中选择，其中 OBJECT='hat' AND (Attribute='Color" AND 值=“红色”）AND（属性=“尺寸”AND 值=“中度”）；

并且，此查询将产生比应有的更多行（即，所有红色和所有中等大小的项目）。

从 Photo_Attributes 中选择，其中 OBJECT='hat' AND (Attribute='Color" AND 值=“红色”）OR（属性=“尺寸”AND 值=“中度”）；

编写此内容的最佳方法是什么 - 以及 - 有没有办法在 SELECT 语句中不使用 JOIN 的情况下完成此操作？我想知道后者是因为查询将以编程方式生成（在nodejs中），并且属性值对的数量可以从一个到多个，我想我也可以使用嵌套查询，从记录集中剔除，但这似乎同样效率低下。

原文

Sorry I couldn't think of a better way to title this. In Amazon's SimpleDB, an item can have multiple values in the same column, so it's possible to select only those items that have all of the attributes being sought.

In MySQL, let's say the following table ("Photo_Attributes") contains an unlimited number of attributes for photographs that are contained in another table ("Photos"), and that the two tables are joined by Item_Number.

And, let's say I wanted to find a hat whose color was red and size was medium, which in this case would be ITEM_ID "ABC" and not "OPQ".

+-----+----------+--------+-----------+-------+  
| ID  | Item_ID  | Object | Attribute | Value |  
+-----+----------+--------+-----------+-------+  
|  1  |  ABC     | Hat    | Color     | Red   |  
+-----+----------+--------+-----------+-------+  
|  2  |  FGH     | Pants  | Color     | Blue  |  
+-----+----------+--------+-----------+-------+  
|  3  |  FGH     | Pants  | Size      | Large |  
+-----+----------+--------+-----------+-------+  
|  4  |  LMN     | Shirt  | Color     | Red   |  
+-----+----------+--------+-----------+-------+  
|  5  |  ABC     | Hat    | Size      | Med   |  
+-----+----------+--------+-----------+-------+  
|  6  |  LMN     | Shirt  | Size      | Med   |  
+-----+----------+--------+-----------+-------+  
|  7  |  OPQ     | Hat    | Color     | White |  
+-----+----------+--------+-----------+-------+  
|  8  |  OPQ     | Hat    | Size      | Med   |  
+-----+----------+--------+-----------+-------+

The following query would yield no results because each row contains only one Attribute and one Value.

SELECT FROM Photo_Attributes WHERE OBJECT='hat' AND (Attribute='Color" AND
Value='Red") AND (Attribute='Size' AND Value='Med');

And, this query would produce more rows than it should (i.e., all red and all medium-sized items).

SELECT FROM Photo_Attributes WHERE OBJECT='hat' AND (Attribute='Color" AND
Value='Red") OR (Attribute='Size' AND Value='Med');

What's the best way to write this -and- is there a way to do it without using JOIN in the SELECT statement? I'm wondering the latter because the query would be programmatically generated (in nodejs) and the number of Attribute-Value pairs could range from one to several. I figure I could also use nested queries, culling from recordset, but that seems equally inefficient.

分享到QQ

分享到微博

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

发布评论

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

楠木可依 2024-12-04 03:19:25

SELECT pa1.Item_ID
    FROM Photo_Attributes pa1
        INNER JOIN Photo_Attributes pa2
            ON pa1.Item_ID = pa2.Item_ID
                AND pa2.Attribute = 'Size'
                AND pa2.Value = 'Med'
    WHERE pa1.Object = 'Hat'
        AND pa1.Attribute = 'Color'
        AND pa1.value = 'Red'

假设您的名称/值对没有重叠（例如，您永远不会有 Size/Red 或 Color/Med），您也可以这样做。

SELECT pa.Item_ID
    FROM Photo_Attributes pa
    WHERE pa.Object = 'Hat'
        AND pa.Attribute IN ('Size', 'Color')
        AND pa.Value IN ('Med', 'Red')
    GROUP BY pa.Item_ID
    HAVING COUNT(DISTINCT Value) = 2

SELECT pa1.Item_ID
    FROM Photo_Attributes pa1
        INNER JOIN Photo_Attributes pa2
            ON pa1.Item_ID = pa2.Item_ID
                AND pa2.Attribute = 'Size'
                AND pa2.Value = 'Med'
    WHERE pa1.Object = 'Hat'
        AND pa1.Attribute = 'Color'
        AND pa1.value = 'Red'

Assuming no overlap in your name/value pairs (e.g., You'd never have Size/Red or Color/Med), you could probably also do something like this.

SELECT pa.Item_ID
    FROM Photo_Attributes pa
    WHERE pa.Object = 'Hat'
        AND pa.Attribute IN ('Size', 'Color')
        AND pa.Value IN ('Med', 'Red')
    GROUP BY pa.Item_ID
    HAVING COUNT(DISTINCT Value) = 2

回复收藏 0 原文

~没有更多了~