在MySQL中基于两个表进行选择

Question

I have two tables.

diseases

-----------------------------
| ID  |  NAME               |
-----------------------------
| 1   | Disease 1           |
| 2   | Disease 2           |
| 3   | Disease 3           |

疾病症状

-----------------------------
| DISEASE_ID  | SYMPTOM_ID  |
-----------------------------
| 1           | 1           |
| 1           | 2           |
| 1           | 3           |
| 1           | 4           |
| 2           | 1           |
| 2           | 2           |

我希望选择所有具有症状1或2和3或4的疾病。

我尝试过：

SELECT * 
 FROM diseases_symtoms 
WHERE (symptoms = '1' OR symptoms = '2') 
  AND (symptoms = '3' OR symptoms = '4')

并且：

SELECT * 
  FROM diseases_symtoms 
  WHERE symptoms IN ('1','2') 
    AND symptoms IN ('3','4')

...但它并没有起作用。

- Deyan

Answer 1

请记住，SELECT只能一次检查一行。这两个查询的作用就好像你可以同时检测到一个1和一个3（例如），这是不可能的。

要一次考虑多行，您可以将表连接到两个单独的副本，或者尝试像这样进行分组：

SELECT diseases.*
FROM diseases
INNER JOIN diseases_symptoms ON (disases_symptoms.disease_id = diseases.disease_id)
GROUP BY diseases.disease_id
HAVING SUM(IF(symptoms = 1 OR symptoms = 2, 1, 0) > 0 AND SUM(IF(symptoms = 3 OR symptoms = 4, 1, 0) > 0

- VoteyDisciple

---

谢谢VoteyDisciple！它工作得很完美，但需要稍作修改：SELECT diseases.* FROM diseases INNER JOIN diseases_symptoms ON (disases_symptoms.disease_id = diseases.disease_id) GROUP BY diseases.disease_id HAVING SUM(IF(symptoms = 1 OR SYMPTOMS = 2, 1, 0)) > 0 AND SUM(IF(symptoms = 3 OR SYMPTOMS = 4, 1, 0)) > 0再次感谢！！！ - Deyan

哎呀，当然，那就是我想要的。我已经进行了编辑以备后用。 - VoteyDisciple

Answer 2

SELECT d.* FROM diseases AS d
INNER JOIN disease_symptoms AS s1 ON s1.DISEASE_ID = d.ID WHERE SYMPTOM_ID IN (1, 2)
INNER JOIN disease_symptoms AS s2 ON s2.DISEASE_ID = d.ID WHERE SYMPTOM_ID IN (3, 4)
GROUP BY d.ID

- Zebra North

---

这个不满足“1或2且3或4”。 - Salil

哎呀，我刚刚注意到我误读了问题，并已编辑帖子以反映该条件。 - Zebra North

Answer 3

你可以尝试...

SELECT DISTINCT *
    FROM diseases
    WHERE EXISTS (SELECT *
                       FROM disease_symptoms
                       WHERE disease.disease_id = disease_symptoms.disease_id AND
                             symptom_id IN (1,2)) AND
          EXISTS (SELECT *
                       FROM disease_symptoms
                       WHERE disease.disease_id = disease_symptoms.disease_id AND
                             symptom_id IN (3,4));

- Brian Hooper