检索 SQL 数据库中彼此完全兼容的行

Question

我的数据库中有这个简单的结构

CREATE TABLE species (
  _id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE compatibility (
  _id INTEGER PRIMARY KEY,
  speciesA INTEGER,
  speciesB INTEGER,
  compatibility TINYINT NOT NULL
);

我想指出的是，speciesA 和speciesB 是复合唯一的，以防止重复信息。例如，如果我的数据库中只有 5 个物种，则兼容性表将如下所示：

INSERT INTO species VALUES (1, 'EspecieA');
INSERT INTO species VALUES (2, 'EspecieB');
INSERT INTO species VALUES (3, 'EspecieC');
INSERT INTO species VALUES (4, 'EspecieD');
INSERT INTO species VALUES (5, 'EspecieD');

INSERT INTO compatibility VALUES (null, 1, 2, 1);
INSERT INTO compatibility VALUES (null, 1, 3, 1);
INSERT INTO compatibility VALUES (null, 1, 4, 1);
INSERT INTO compatibility VALUES (null, 1, 5, 0);
INSERT INTO compatibility VALUES (null, 2, 3, 1);
INSERT INTO compatibility VALUES (null, 2, 4, 1);
INSERT INTO compatibility VALUES (null, 2, 5, 0);
INSERT INTO compatibility VALUES (null, 3, 4, 1);
INSERT INTO compatibility VALUES (null, 3, 5, 1);
INSERT INTO compatibility VALUES (null, 4, 5, 1);

我需要编写一个查询，从给定的物种列表返回彼此完全兼容的物种列表，这意味着结果列表中的所有物种必须与提供的列表中的所有物种兼容。所提供的物种不得出现在结果列表中。

我尝试了以下查询，但它只返回与至少一种提供的物种兼容的物种：

SELECT id, name
FROM species s
WHERE s.id NOT IN (
    SELECT IF(speciesA NOT IN (1,2,3), speciesA, speciesB) AS specie
    FROM compatibility 
    WHERE (speciesA IN (1,2,3)
    AND compatible IN (0)) OR (speciesB IN (1,2,3)
    AND compatible IN (0))
)
AND s.id NOT IN (1,2,3);

如何修改此查询以获得彼此完全兼容的物种列表？

对于上面的查询，预期结果应该是仅包含物种 4 的物种列表。物种 1、2、3 被排除在提供的列表中，并且 5 应被排除，因为与物种 1 不兼容并且2.

任何帮助或建议将不胜感激。谢谢！

Answer 1

我们可以通过过滤和聚合来解决这个问题。

select spec
from (
    select 
        case when speciesA in (1, 2, 3) then speciesA else speciesB end as ref,
        case when speciesA in (1, 2, 3) then speciesB else speciesA end as spec
    from compatibility c
) c
where ref in (1, 2, 3)
group by spec
having count(*) = 3

子查询将参考物种 (ref) 和兼容物种 (spec) 放在两个不同的列中。然后，我们可以筛选您感兴趣的三个参考物种，按兼容物种进行分组，最后保留与 having 子句完全匹配的组。

如果您也想知道物种名称，我们可以加入：

select c.spec, s.name
from (
    select 
        case when speciesA in (1, 2, 3) then speciesA else speciesB end as ref,
        case when speciesA in (1, 2, 3) then speciesB else speciesA end as spec
    from compatibility c
) c
inner join species s on s._id = c.spec
where c.ref in (1, 2, 3)
group by c.spec
having count(*) = 3

DB Fiddle 演示

Answer 2

这仅包括与兼容性= 1明确链接的物种（即默认情况下假设物种不兼容）

SELECT s._id, s.name
FROM species s
INNER JOIN compatibility c ON
    (s._id = c.speciesA AND c.speciesB IN (1, 2))
    OR (s._id = c.speciesB AND c.speciesA IN (1, 2))
WHERE c.compatibility = 1
AND s._id NOT IN (1, 2)

这将包括除那些明确与兼容性 = 0 相关的物种之外的所有物种（即默认情况下假设物种是兼容的）

SELECT s._id, s.name
FROM species s
LEFT JOIN compatibility c ON
    ((s._id = c.speciesA AND c.speciesB IN (1, 2))
    OR (s._id = c.speciesB AND c.speciesA IN (1, 2)))
    AND c.compatibility = 0
WHERE c._id IS NULL
AND s._id NOT IN (1, 2)

无论其中哪一个更适合您的逻辑，我还建议您在 compatibility.speciesA 和 compatibility.speciesB 列上添加索引，以优化查询性能。 p>