在基于两个表的MySQL中选择
在基于两个表的MySQL中选择
提问于 2010-06-11 20:46:42
我有两张桌子。
疾病
-----------------------------
| ID | NAME |
-----------------------------
| 1 | Disease 1 |
| 2 | Disease 2 |
| 3 | Disease 3 |diseases_symptoms
-----------------------------
| 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')...but它不工作。
回答 3
Stack Overflow用户
回答已采纳
发布于 2010-06-11 20:51:45
请记住，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) > 0Stack Overflow用户
发布于 2010-06-11 20:52:44
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.IDStack Overflow用户
发布于 2010-06-11 21:02:02
你可以试试..。
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));页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3022799
复制相关文章
相似问题
腾讯云开发者
