MySQL从键/值对表中选择 [英] MySQL select from key/value pairs table

问题描述

从包含键/值对的表中选择数据时遇到很多问题.

I'm having a lot of problems with selecting data from a table containing a key/value pairs.

我发现了一些与我的问题类似的问题，但不幸的是，我什至没有找到解决办法.

I have found a few questions similar to mine, but unfortunately I'm not even close to find a solution.

需要MySQL从存储键值对的表中进行查询的查询

如何从键值对中选择数据桌子

无论如何，假设这是我的桌子:

Anyway, let suppose that this is my table:

user_id | item_id | item_amount
--------------------------------
 1      | 12      | 5
 1      | 15      | 10 
 2      | 12      | 20
 2      | 15      | 30
 3      | 12      | 1
 3      | 30      | 5

现在，我想搜索ID为12和15的项目中有5到50个的SELECT用户.

Now, I want to perform a search to SELECT users who are having between 5 and 50 of items of id 12 AND 15.

我(非常天真)尝试类似

I was (very naively) trying something like

SELECT user_id 
FROM user_item 
WHERE (item_id = 12 AND item_amount BETWEEN 5 AND 50) 
AND (item_id = 15 AND item_amount BETWEEN 5 AND 50)

当然，它不起作用.谢谢您的反馈.

Of course, it doesn't work. Thank you for any feedback.

推荐答案

您很亲密:

SELECT user_id
FROM user_item
WHERE (item_id = 12 AND item_amount BETWEEN 5 AND 50) OR
      (item_id = 15 AND item_amount BETWEEN 5 AND 50)
GROUP BY user_id
HAVING COUNT(DISTINCT item_id) = 2;

WHERE子句过滤到其中任一条件匹配的行. HAVING保证两个条件都匹配给定用户.

The WHERE clause filters to rows where either condition matches. The HAVING guarantees that both conditions match for a given user.

这篇关于MySQL从键/值对表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！