Mysql:在其他条件下使用 HAVING 而不是 Multiple Self Join

Question

我有一个看起来像这样的表

ailment_symptoms 
+----+----------+-----------+
|id  |ailment_id|symptom_id |
+----+----------+-----------+
|1   | 1        |  1        |
|2   | 1        |  2        |
|3   | 2        |  1        |
|4   | 2        |  3        |
|5   | 3        |  3        |
|6   | 3        |  2        |
|7   | 4        |  1        |
|8   | 4        |  2        |
+----+----------+-----------+

我想选择所有具有symptom_id=1 AND symptom id = 2 的 ailment_id，有人建议使用下面的代码。

SELECT s.ailment_id
FROM ailment_symptoms s
WHERE s.symptom_id in (1, 2)
GROUP BY s.ailment_id
HAVING COUNT(DISTINCT s.symptom_id) = 2

我现在的问题是，假设该表有另一个名为 symptom_intesity 的列，我想选择一个疾病 ID(symptom_id1 = 1 AND s1.symptom _intensity = 'acute') AND (symptom_id2 = 2 AND symptom_intensity='slight') AND (...) OR (...)像那样。使用多个自连接，它看起来像这样:

SELECT t1.ailment_id
FROM ailment_symptoms t1
INNER JOIN ailment_symptoms t2 ON t1.ailment_id = t2.ailment_id
INNER JOIN ailment_symptoms t3 ON t1.ailment_id = t3.ailment_id
INNER JOIN ailment_symptoms t4 ON t1.ailment_id = t4.ailment_id
   /*---- more inner joins as there are symptoms ----*/
WHERE (t1.symptom_id =  '1' AND t1.intensity = 'acute')
AND (t2.symptom_id =  '10')
AND (t3.symptom_id =  '3' AND t2.intensity = 'slight')
AND (t4.symptom_id =  '4')
 /*----and so on ----/

我如何使用第一个查询的结构实现此目的。请我也很感激解释，所以我可以根据我的需要修改它。谢谢。

Answer 1

如果我没有正确理解您的目标，我通常会在这种情况下使用串联，即当逻辑表达式取决于某些条件时:

     SELECT ailment_id
      FROM ailment_symptoms 
     WHERE symptom_id||symptom_intesity='1acute' OR symptom_id||symptom_intesity='2slight' OR symptom_id = '10' OR symptom_id =  '4'
    GROUP BY ailment_id
    HAVING COUNT(DISTINCT symptom_id) = 2