在不同的行上选择满足不同条件的值? [英] Select values that meet different conditions on different rows?

问题描述

这是一个非常基本的查询，我无法弄清楚....

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 AND 3 的不同用户 ID.使用上面的示例，我想要返回的唯一结果是 userid 1. 怎么做我这样做?

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;

<小时>

致任何阅读本文的人:我的回答简单明了，并获得了已接受"状态，但请务必阅读 答案 由@cletus 给出.它的性能要好得多.

---

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 万行的查询(PostTags 的 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.

这篇关于在不同的行上选择满足不同条件的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！