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

Question

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

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

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

Answer 1

执行此操作的经典方法是将其视为关系划分问题。

英语：选择不缺少任何所需 roleid 值的用户。

我假设您有一个 UserRole 表引用的 Users 表，并且假设所需的 roleid 值位于表中：

create table RoleGroup(
  roleid int not null,
  primary key(roleid)
)
insert into RoleGroup values (1);
insert into RoleGroup values (2);
insert into RoleGroup values (3);

我还将假设所有相关列都不可为 NULL，因此 IN 不会出现任何意外或不存在。 下面是一个表达上面英文的 SQL 查询：

select userid from Users as U
where not exists (
  select * from RoleGroup as G
  where not exists (
    select R.roleid from UserRole as R
    where R.roleid = G.roleid
    and R.userid = U.userid
  )
);

另一种编写方式是这样

select userid from Users as U
where not exists (
  select * from RoleGroup as G
  where G.roleid not in (
    select R.roleid from UserRole as R
    where R.userid = U.userid
  )
);

这可能会也可能不会有效，具体取决于索引、平台、数据等。在网络上搜索“关系划分”，您会发现很多。

Answer 2

假设 userid、roleid 包含在唯一索引中（意味着不能有 2 条记录，其中 userid = x 且 roleid = 1

select count(*), userid from t
where roleid in (1,2,3)
group by userid
having count(*) = 3

Answer 3

select userid from userrole where userid = 1
intersect
select userid from userrole where userid = 2
intersect
select userid from userrole where userid = 3

这不就能解决问题了吗？ 对于典型的关系数据库来说，这是一个多好的解决方案？ 查询优化器会自动优化吗？

Answer 4

如果您在这里需要任何类型的通用性（不同的 3 角色组合或不同的 n 角色组合）...我建议您为您的角色使用位屏蔽系统并使用按位运算符来执行查询...

Answer 5

好吧，我对此表示反对，所以我决定对其进行测试：

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

CREATE INDEX ON userrole (roleid);

运行以下命令：

<?php
ini_set('max_execution_time', 120); // takes over a minute to insert 500k+ records

$start = microtime(true);

echo "<pre>\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 "</pre>\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.312s

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.016s

没错。 我提出的连接版本比聚合版本快二十倍。

抱歉，但我这样做是为了在现实世界中生活和工作，在现实世界中我们测试 SQL，结果不言而喻。

这其中的原因应该是很清楚的。 聚合查询的成本将随着表的大小而变化。 每行都通过 HAVING 子句进行处理、聚合和过滤（或不进行处理）。 连接版本将（使用索引）根据给定角色选择用户子集，然后对照第二个角色检查该子集，最后对照第三个角色检查该子集。 每个选择（在关系代数术语）适用于越来越小的子集。 由此您可以得出结论：

加入版本的性能会随着匹配发生率的降低而变得更好。

如果只有 500 个用户（上面的 50 万个样本中）具有三个指定的角色， join 版本将会变得更快。 聚合版本不会（并且任何性能改进都是传输 500 个用户而不是 25k 个用户的结果，而加入版本显然也得到了 25k）。

我也很好奇真正的数据库（即 Oracle）将如何处理这个问题。 因此，我基本上在 Oracle XE 上重复了相同的练习（与上一个示例中的 MySQL 在同一台 Windows XP 桌面计算机上运行），结果几乎相同。

连接似乎不受欢迎，但正如我所演示的，聚合查询可能会慢一个数量级。

更新：经过一些广泛的测试，情况变得更加复杂，答案将取决于您的数据、数据库和其他因素。 这个故事的寓意是测试、测试、测试。

Answer 6

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

---

只是大声思考，另一种编写自连接的方法 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 7 月数据转储），并在 1.08 秒内返回结果。 在一个为 innodb_buffer_pool_size 分配了足够内存的体面服务器上，它应该会更快。

_{对于阅读本文的任何人：我的答案简单明了，并获得了“已接受”状态，但请务必阅读cletus给出的答案。 它具有更好的性能。}