需要一个MySQL查询来从存储键值对的表中进行选择 [英] Need a MySQL query for selecting from a table storing key value pairs

问题描述

我需要以键值对的形式在数据库(mySQL)中存储一些项目及其属性.我打算按照以下步骤进行操作.

I need to store few items and its properties in form of a key value pairs in the database (mySQL). I am planning to do it as following.

我将使用两个表items和item_properties.

items

 itemId | itemName 
-------------------
 1923   | AC
 1235   | Fridge
 8273   | Heater

item_properties

 itemId | property    | value
--------------------------------
 1923   | effect      | cooling
 1923   | consumption | efficient
 1923   | type        | split
 1235   | effect      | cooling
 1235   | volume      | 20 liters
 8273   | effect      | heating
 8273   | consumption | efficient
 8273   | heatMethod  | coil

现在，如果我必须选择效果"为冷却"的项目，则可以使用以下查询进行操作(结果将为我提供"AC"和冰箱").

Now, if I have to select items whose 'effect' is 'cooling', I can do that using following query (which will give me 'AC' and 'Fridge' in result).

SELECT itemName FROM items i, item_properties p 
WHERE i.itemId=p.itemId 
AND (p.property = 'effect' AND p.value ='cooling');

我想知道如何编写查询来选择与多个属性相匹配的项目，例如

I would like to know how write queries to select items that match multiple properties like

• 选择所有效果"为冷却"且消耗"为有效"的项目(与项目"AC"匹配).
• 选择所有类型"为"split"或"heatMethod"为"coil"或"consumption"为"effecient"的物品(将匹配"AC"和"Heater"的物品).

请帮助...预先感谢！

Kindly Help... Thanks in advance!!

推荐答案

以下是示例查询:

SELECT
  itemName
FROM
  items i,
JOIN
  item_properties effect
  ON i.itemId = effect.itemId AND effect.property = 'effect'
JOIN
  item_properties consumption
  ON i.itemId = consumption.itemId AND consumption.property = 'consumption'

WHERE effect.value = 'cooling' AND consumption.value = 'efficient';

我将留下oR查询，您可以自己尝试一下.只是添加更多表并使用OR代替WHERE中的AND.

I'll leave the oR query as something you can try yourself. It's simply adding more tables and using OR instead of AND in the WHERE.

这篇关于需要一个MySQL查询来从存储键值对的表中进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！