Maison > Questions et réponses > le corps du texte
J'ai cette structure simple dans ma base de données
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 );
Je tiens à souligner que les espècesA et les espècesB sont composites uniques pour éviter la duplication des informations. Par exemple, si je n'avais que 5 espèces dans ma base de données, le tableau de compatibilité ressemblerait à ceci :
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);
Je dois écrire une requête qui renvoie une liste d'espèces entièrement compatibles entre elles à partir d'une liste d'espèces donnée, ce qui signifie que toutes les espèces de la liste résultante doivent être compatibles avec toutes les espèces de la liste fournie. Les espèces fournies ne doivent pas apparaître dans la liste des résultats.
J'ai essayé la requête suivante mais elle ne renvoie que les espèces compatibles avec au moins une des espèces fournies :
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);
Comment puis-je modifier cette requête pour obtenir une liste d'espèces entièrement compatibles entre elles ?
Pour la requête ci-dessus, le résultat attendu doit être une liste d'espèces contenant uniquement l'espèce 4. Les espèces 1, 2, 3 sont exclues de la liste fournie, et 5 devraient être exclues en raison d'une incompatibilité avec les espèces 1 et 2.
Toute aide ou conseil serait grandement apprécié. Merci!
P粉6847208512023-09-10 10:46:11
Nous pouvons résoudre ce problème grâce au filtrage et à l'agrégation.
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(*) = 3La sous-requête
fera référence à l'espèce (clause ref
) 和兼容物种 (spec
) 放在两个不同的列中。然后,我们可以筛选您感兴趣的三个参考物种,按兼容物种进行分组,最后保留与 having
) qui correspond exactement au groupe
Si vous souhaitez également connaître le nom de l'espèce, nous pouvons 加入
:
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
P粉7399424052023-09-10 00:55:29
Cela inclut uniquement les espèces explicitement liées à compatibilité=1 (c'est-à-dire que les espèces sont supposées incompatibles par défaut)
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)
Cela inclura toutes les espèces sauf celles explicitement associées à compatibilité = 0 (c'est-à-dire que les espèces sont supposées compatibles par défaut)
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)
Celui qui convient le mieux à votre logique, je vous recommande également de