如何编写SQL查询获取与指定物种完全兼容的物种列表

我的数据库结构如下：

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是复合唯一键，用于避免重复记录。

现有数据示例：

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

需要实现的SQL查询需求：

• 返回与给定物种列表（示例中为1,2,3）全部兼容的物种
• 结果中不能包含给定列表内的物种

我尝试了以下查询，但它仅能返回与给定物种中至少一个兼容的物种：

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不兼容被排除。如何修改查询以满足要求？

可以通过统计目标物种与给定列表中所有物种的兼容匹配次数来实现，核心是确保目标物种与列表中每个物种都存在兼容记录。

方案一：分组统计兼容次数 #

SELECT s._id, s.name
FROM species s
JOIN compatibility c ON 
    (c.speciesA = s._id AND c.speciesB IN (1,2,3)) 
    OR (c.speciesB = s._id AND c.speciesA IN (1,2,3))
WHERE 
    s._id NOT IN (1,2,3)
    AND c.compatibility = 1
GROUP BY s._id, s.name
HAVING COUNT(DISTINCT CASE 
    WHEN c.speciesA IN (1,2,3) THEN c.speciesA 
    ELSE c.speciesB 
END) = 3;

逻辑说明：

1. 关联匹配：将物种表与兼容性表关联，筛选出当前物种和给定列表物种的所有兼容记录（compatibility=1）。
2. 排除给定物种：通过WHERE s._id NOT IN (1,2,3)直接排除输入列表内的物种。
3. 分组统计：按目标物种分组后，统计该物种与给定列表中不同物种的兼容次数。
4. 筛选全兼容物种：HAVING子句要求统计数量等于给定列表的物种总数（示例中为3），确保该物种与列表中每一个物种都兼容。

方案二：反选排除不兼容物种 #

SELECT s._id, s.name
FROM species s
WHERE 
    s._id NOT IN (1,2,3)
    -- 排除与给定列表中任意物种不兼容的情况
    AND NOT EXISTS (
        SELECT 1
        FROM compatibility c
        WHERE 
            (c.speciesA = s._id AND c.speciesB IN (1,2,3) AND c.compatibility = 0)
            OR (c.speciesB = s._id AND c.speciesA IN (1,2,3) AND c.compatibility = 0)
    )
    -- 确保与给定列表所有物种都有兼容记录
    AND (
        SELECT COUNT(DISTINCT CASE 
            WHEN c.speciesA IN (1,2,3) THEN c.speciesA 
            ELSE c.speciesB 
        END)
        FROM compatibility c
        WHERE 
            (c.speciesA = s._id AND c.speciesB IN (1,2,3) AND c.compatibility = 1)
            OR (c.speciesB = s._id AND c.speciesA IN (1,2,3) AND c.compatibility = 1)
    ) = 3;

逻辑说明：

1. 排除不兼容物种：通过NOT EXISTS子句过滤掉与给定列表中任意物种存在不兼容记录的物种。
2. 验证全兼容：通过子查询统计目标物种与给定列表中兼容的物种数量，确保数量等于列表总数，避免出现与列表部分物种无交互记录的情况。

内容的提问来源于stack exchange，提问作者Nathan Lo Sabe