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

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

假设我有一个像这样的两列表:

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

我想得到所有具有roleids1,2和3的不同用户ID .使用上面的例子,我想要返回的唯一结果是userid1.我该怎么做？

1> cletus..：

---

好的,我对此投了反对,所以我决定测试一下:

CREATE TABLE userrole (
  userid INT,
  roleid INT,
  PRIMARY KEY (userid, roleid)
);

CREATE INDEX ON userrole (roleid);

运行这个:

\n";
mysql_connect('localhost', 'scratch', 'scratch');
if (mysql_error()) {
    echo "Connect error: " . mysql_error() . "\n";
}
mysql_select_db('scratch');
if (mysql_error()) {
    echo "Selct DB error: " . mysql_error() . "\n";
}

$users = 200000;
$count = 0;
for ($i=1; $i<=$users; $i++) {
    $roles = rand(1, 4);
    $available = range(1, 5);
    for ($j=0; $j<$roles; $j++) {
        $extract = array_splice($available, rand(0, sizeof($available)-1), 1);
        $id = $extract[0];
        query("INSERT INTO userrole (userid, roleid) VALUES ($i, $id)");
        $count++;
    }
}

$stop = microtime(true);
$duration = $stop - $start;
$insert = $duration / $count;

echo "$count users added.\n";
echo "Program ran for $duration seconds.\n";
echo "Insert time $insert seconds.\n";
echo "

\n"; function query($str) { mysql_query($str); if (mysql_error()) { echo "$str: " . mysql_error() . "\n"; } } ?>

输出:

499872 users added.
Program ran for 56.5513510704 seconds.
Insert time 0.000113131663847 seconds.

这增加了500,000个随机用户角色组合,大约有25,000个符合所选标准.

第一个查询:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

查询时间:0.312秒

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

查询时间:0.016秒

那就对了.我提出的加入版本比聚合版本快20倍.

抱歉,我这样做是为了生活和工作在现实世界和现实世界中我们测试SQL,结果不言自明.

原因应该很清楚.聚合查询将根据表的大小按比例缩放.通过该HAVING子句处理,聚合和过滤(或不过滤)每一行.连接版本将(使用索引)根据给定角色选择用户的子集,然后针对第二个角色检查该子集,最后针对第三个角色检查该子集.每个选择(在关系代数术语中)都适用于越来越小的子集.由此您可以得出结论:

匹配发生率越低,连接版本的性能就越好.

如果只有500个用户(在上面的500k样本中)具有三个声明的角色,则加入版本将显着加快.聚合版本不会(并且任何性能改进都是由于传输500个用户而不是25k,加入版本显然也是如此).

我也很想知道真正的数据库(即Oracle)如何处理这个问题.所以我基本上在Oracle XE上重复了相同的练习(在与前一个例子中的MySQL相同的Windows XP台式机上运行),结果几乎相同.

加入似乎不受欢迎,但正如我所证明的,聚合查询可能会慢一个数量级.

更新:经过一些广泛的测试后,图片更复杂,答案取决于您的数据,数据库和其他因素.故事的寓意是测试,测试,测试.

---

只是为了挑剔:请注意,当表中有双重条目时,两个查询不相等,如果在role_id列中存在NULL,则结果也可能不同.虽然我猜这个表在两列都有一个pk.

---

连接几乎总是比等效的聚合查询更快.但除非有速度要求(通过剖析确定),否则我不会牺牲清晰度......

---

如果将t2和t3的roleId测试移动到WHERE子句,它会有什么不同吗？在我看来(至少在概念上)它们属于那里,我希望它不会影响性能 - 但我还没有测试过.

2> Bill Karwin..：

---

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万行(PostTags的Stack Overflow July数据转储)的查询,并在1.08秒内返回结果.在分配了足够内存的体面服务器上innodb_buffer_pool_size,它应该更快.