sql - 选择具有多个关系的行

Question

鉴于此模式:

CREATE TABLE t (
  id int,
  name varchar(200),
  primary key (id)
);

CREATE TABLE t2 (
  id int,
  name varchar(200),
  primary key (id)
);

CREATE TABLE rel (
  id1 int,
  id2 int,
  value int,
  primary key (id1, id2),
  foreign key (id1) REFERENCES t(id),
  foreign key (id2) REFERENCES t2(id)
);

我应该如何从 t 中查询满足与 t2 超过 1 个关系的行？这听起来很简单，但我不确定在这种情况下最佳做法是什么。我会用查询更好地解释:

SELECT t.id, t.name
FROM t
INNER JOIN rel ON (t.id = rel.id1)
INNER JOIN t2 ON (t2.id = rel.id2)
WHERE (rel.id2 = 1 AND rel.value = 1)

当我需要查找满足一种关系的行时，上面的方法很有效。但是现在:

SELECT t.id, t.name
FROM t
INNER JOIN rel ON (t.id = rel.id1)
INNER JOIN t2 ON (t2.id = rel.id2)
WHERE (rel.id2 = 1 AND rel.value = 1)
AND (rel.id2 = 2 AND rel.value = 2)

如果有 2 个关系，此查询将永远无法工作，因为连接的行永远不会在一行中有 2 个不同的关系，因此它将始终返回 0 行。

到目前为止，我一直在使用的解决方案是寻找 IN 关系结果集中的 t.id:

SELECT t.id, t.name
FROM t
WHERE (t.id IN (SELECT id1 FROM rel WHERE rel.id2 = 1 AND rel.value = 1))
AND (t.id IN (SELECT id1 FROM rel WHERE rel.id2 = 2 AND rel.value = 2))

这行得通，但是没有更好的解决方法吗？我觉得我写了太多的 SQL，并且对每个项目执行一个子查询对于如此简单的事情来说似乎有点过分了。

这是 SQL Fiddle

Answer 1

我可能过度简化了这个问题，但你能不能在你的 where 子句中将 AND 更改为 OR 并使用 GROUP BY/HAVING 获取给定条件的关系数。例如

SELECT  t.id, t.name
FROM    t
        INNER JOIN rel 
            ON t.id = rel.id1
        INNER JOIN t2 
            ON t2.id = rel.id2
WHERE   (rel.id2 = 1 AND rel.value = 1)
OR      (rel.id2 = 2 AND rel.value = 1)
GROUP BY t.Id, t.Name
HAVING COUNT(*) = 2;

尽管我会将 where 子句重写为:

WHERE   rel.Value = 1 
AND     rel.ID2 IN (1, 2)

编辑

我不会再用编辑过的问题重写上面的 where 子句。会变成

WHERE   (rel.id2 = 1 AND rel.value = 1)
OR      (rel.id2 = 2 AND rel.value = 2)