我该如何编写这个查询？

Question

我有以下的mysql表：

map_id - module_id - category_id
1      -     3     -     6
2      -     3     -     9
3      -     3     -     11
4      -     4     -     6
5      -     4     -     9
6      -     4     -     12

map_id是主键。我有一个category_id数组的列表。

我的选择条件是：

• 如果category_id数组包含6,9，则应返回module_id=3,4。
• 如果category_id数组包含6,9,11，则仅应返回module_id=3。

现在，如果运行简单的WHERE...IN查询，则会选择所有行，这不是我想要的。

编辑：实际上，我正在寻找更动态的解决方案。也许我的问题没有表达清楚（这是我的第一个问题）。

假设我有以下category_id：

$cat = array(6,9)

如果查询

"SELECT module_id FROM maptable WHERE category_id IN (".implode(',', $cat).")"

然后就会得到module_id=3,4的行。

另一方面，如果数组为

$cat = array(6,9,11)

并且我运行相同的查询，则再次获得相同的结果。但这次我只想要module_id=3的行。

- Salman

Answer 1

SQL并不是为这种查询设计的，但它确实可以完成。

SELECT module_id FROM
(SELECT module_id FROM table WHERE category_id = 6) cat_6
JOIN (SELECT module_id FROM table WHERE category_id =  9)  cat_9 USING (module_id)
JOIN (SELECT module_id FROM table WHERE category_id = 11) cat_11 USING (module_id)

请确保在category_id, module_id上建立一个索引，并在module_id, category_id上建立第二个索引。最好使这些索引都是唯一的。

- Ariel

---

+1，我认为应该有一个“group by”子句来对“module_id”进行分组？ - Mahmoud Gamal

解决方案看起来不错，但为什么要在c、m和m、c上建立索引，而不是每个列上只建立一个索引？你有一个可以解释这个问题的链接吗？这将非常好 :) - Flo

它可以工作。查询看起来很奇怪，我对此一无所知。顺便说一下，我在module_id、category_id上有一个唯一索引。是的。 - Salman

如果 category_id, module_id 是唯一的，则不需要使用 group by。 - Ariel

@Flo 组合索引允许MySQL执行称为索引合并连接的操作，其中它组合了module_id（例如）索引，然后能够直接检查category_id是否是所需的，而无需扫描所有行。使用两个索引可以让MySQL选择最佳方式（有时按category_id搜索并获取module_ids列表更好，有时它已经从先前的子查询中获取了module_id列表，然后想要检查category_id）。 - Ariel

@Flo基本上，如果你知道你总是会检查两列，那么如果它是一个组合索引，速度会更快 - 它不能在同一查询中使用两个索引，因此（如果它们是两个单独的索引）它将使用其中一个，而不是两个都用。最后，做一个组合索引让你把它变成唯一的，这将有助于查询的高效工作（因为它知道它不必继续检查重复项，因为不能有）。 - Ariel

Answer 2

当你使用WHERE...IN时，它意味着“任何一个in(...)的值”

你可以使用AND代替

- triclosan

Answer 3

WHERE ((category_id = 6 OR category_id=9) AND module_id=3) OR ((category_id = 6 OR category_id=9 OR category_id = 11) AND (module_id=3 OR module_id=4))

?

可能有更简单的编写方式，但我想你明白我的意思 :-)

- Tom van der Woerdt

---

我认为他想要的是（但描述得很糟糕）获取包含他正在查找的类别的所有模块的module_ids。因此，6,9返回3和4，但6,9,11仅返回3。因此，在where子句中包括module_ids没有意义，因为map_id似乎只是一个不包含任何信息的递增索引。 - Flo

你太字面理解他的问题了。第三和第四模块只是例子，不应该放在where语句中。 - Ariel

那么你的问题问错了;-) 你确定你不能将其适应到你的情况吗？ - Tom van der Woerdt

Answer 4

你可以尝试：

SELECT * FROM `table`
WHERE (`module_id` IN(3,4) AND `category_id` IN(6,9))
   OR (`module_id` IN(3) AND `category_id` IN(6,9,11))

- Mohit Bumb