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

问题描述：

这是我不知道的非常基本的查询....

This is a very basic query I can't figure out....

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

Let's say I have a two column table like this:

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

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

I want to get all distinct userids that have roleids 1, 2 AND 3. Using the above example, the only result I want returned is userid 1. How do I do this?

答

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

对于阅读此书的任何人:我的回答很简单明了，并且具有已接受"状态，但是请务必阅读

To anyone reading this: my answer is simple and straightforward, and got the 'accepted' status, but please do go read the answer given by @cletus. It has much better performance.

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

Justing thinking out loud, another way to write the self-join described by @cletus is:

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运行它，然后在所有三个表的注释中看到使用索引"，这意味着它正在读取索引，甚至不必触摸数据行.

This might be easier to read for you, and MySQL supports comparisons of tuples like that. MySQL also knows how to utilize covering indexes intelligently for this query. Just run it through EXPLAIN and see "Using index" in the notes for all three tables, which means it's reading the index and doesn't even have to touch the data rows.

我在Macbook上使用MySQL 5.1.48在210万行(PostTag的Stack Overflow 7月数据转储)中运行了此查询，并在1.08秒内返回了结果.在分配了足够的内存给innodb_buffer_pool_size的体面的服务器上，它应该甚至更快.

I ran this query over 2.1 million rows (the Stack Overflow July data dump for PostTags) using MySQL 5.1.48 on my Macbook, and it returned the result in 1.08 sec. On a decent server with enough memory allocated to innodb_buffer_pool_size, it should be even faster.

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

相关推荐