Rufen Sie Zeilen in einer SQL-Datenbank ab, die vollständig miteinander kompatibel sind

Question

Ich habe diese einfache Struktur in meiner Datenbank

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
);

Ich möchte darauf hinweisen, dass ArtA und ArtB einzigartig sind, um eine Duplizierung von Informationen zu verhindern. Wenn ich beispielsweise nur 5 Arten in meiner Datenbank hätte, würde die Kompatibilitätstabelle so aussehen:

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);

Ich muss eine Abfrage schreiben, die eine Liste von Arten aus einer bestimmten Artenliste zurückgibt, die vollständig miteinander kompatibel sind, was bedeutet, dass alle Arten in der resultierenden Liste mit allen Arten in der bereitgestellten Liste kompatibel sein müssen. Die angegebenen Arten dürfen nicht in der Ergebnisliste erscheinen.

Ich habe die folgende Abfrage ausprobiert, aber sie gibt nur Arten zurück, die mit mindestens einer der bereitgestellten Arten kompatibel sind:

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);

Wie kann ich diese Abfrage ändern, um eine Liste der Arten zu erhalten, die vollständig miteinander kompatibel sind?

Für die obige Abfrage sollte das erwartete Ergebnis eine Artenliste sein, die nur Art 4 enthält. Die Arten 1, 2 und 3 sind von der bereitgestellten Liste ausgeschlossen, und 5 sollte aufgrund der Unverträglichkeit mit den Arten 1 und 2 ausgeschlossen werden.

Jede Hilfe oder jeder Rat wäre sehr dankbar. Danke!

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>