选择在不同行上满足不同条件的值？

Question

这是我无法弄清楚的非常基本的查询。

假设我有一个两列的表格，如下所示：

userid  |  roleid
--------|--------
   1    |    1
   1    |    2
   1    |    3
   2    |    1

我想获取所有具有roleids1、2和3的不同用户ID 。使用上面的示例，我要返回的唯一结果是userid1。我该怎么做？

Answer 1

SELECT userid
FROM UserRole
WHERE roleid IN (1, 2, 3)
GROUP BY userid
HAVING COUNT(DISTINCT roleid) = 3;

任何人阅读本：我的答案是简单明了的，并得到了“接受”的地位，但请不要去阅读答案通过@cletus给出。它具有更好的性能。

只是大声思考一下，@ cletus所描述的编写自联接的另一种方法是：

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid
JOIN userrole t3 ON t2.userid = t3.userid
WHERE (t1.roleid, t2.roleid, t3.roleid) = (1, 2, 3);

这对您来说可能更容易阅读，并且MySQL支持类似的元组比较。MySQL还知道如何针对此查询智能地利用覆盖索引。只需运行它，EXPLAIN并在所有三个表的注释中看到“使用索引”，这意味着它正在读取索引，甚至不必触摸数据行。

我在Macbook上使用MySQL 5.1.48对这个查询进行了210万行查询（PostTag的Stack Overflow
7月数据转储），它在1.08秒内返回了结果。在分配了足够内存的体面服务器上innodb_buffer_pool_size，它甚至应该更快。