Dapatkan semula baris dalam pangkalan data SQL yang serasi sepenuhnya antara satu sama lain

Question

Saya mempunyai struktur mudah ini dalam pangkalan data saya

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

Saya ingin menyatakan bahawa spesiesA dan spesiesB adalah komposit unik untuk mengelakkan pertindihan maklumat. Sebagai contoh, jika saya hanya mempunyai 5 spesies dalam pangkalan data saya, jadual keserasian akan kelihatan seperti ini:

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

Saya perlu menulis pertanyaan yang mengembalikan senarai spesies yang serasi sepenuhnya antara satu sama lain daripada senarai spesies yang diberikan, bermakna semua spesies dalam senarai yang terhasil mestilah serasi dengan semua spesies dalam senarai yang disediakan. Spesies yang disediakan tidak boleh muncul dalam senarai keputusan.

Saya mencuba pertanyaan berikut tetapi ia hanya mengembalikan spesies yang serasi dengan sekurang-kurangnya satu daripada spesies yang disediakan:

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

Bagaimanakah saya boleh mengubah suai pertanyaan ini untuk mendapatkan senarai spesies yang serasi sepenuhnya antara satu sama lain?

Untuk pertanyaan di atas, hasil yang dijangkakan mestilah senarai spesies yang mengandungi hanya spesies 4. Spesies 1, 2, 3 dikecualikan daripada senarai yang disediakan, dan 5 harus dikecualikan kerana ketidakserasian dengan spesies 1 dan 2.

Sebarang bantuan atau nasihat amat kami hargai. Terima kasih!

Answer 1

Kita boleh menyelesaikan masalah ini melalui penapisan dan pengagregatan.

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

Subkueri

akan merujuk kepada spesies (klausa ref) 和兼容物种 (spec) 放在两个不同的列中。然后，我们可以筛选您感兴趣的三个参考物种，按兼容物种进行分组，最后保留与 having) yang sepadan dengan kumpulan

Kalau nak tahu nama spesis pun boleh 加入:

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 Demo

Answer 2

Ini hanya termasuk spesies yang dikaitkan secara jelas dengan keserasian=1 (iaitu spesies diandaikan tidak serasi secara lalai)

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)

Ini akan termasuk semua spesies kecuali spesies yang dikaitkan secara jelas dengan keserasian = 0 (iaitu spesies diandaikan serasi secara lalai)

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)

Mana-mana antara ini lebih sesuai dengan logik anda, saya juga mengesyorkan anda