檢索 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>