我有三张桌子
我想按症状匹配数来排序疾病,并且症状由最终用户输入,并存储在变量$ searchSymptoms中。
我尝试了以下查询并作了一些改动,但结果很奇怪。
SELECT DISTINCT ailments.ailmentName,COUNT(symptoms.symptomID) AS count
FROM ailments LEFT JOIN jnctn_ailments_symptoms ON ailments.ailmentID = jnctn_ailments_symptoms.FK_ailmentID
LEFT JOIN symptoms ON ailments.ailmentID = jnctn_ailments_symptoms.FK_ailmentID
GROUP BY ailments.ailmentName ORDER BY count DESC
结果:
如果有任何答案,我将负责。
问候
最佳答案
该查询可以编写如下:
SELECT ailmentID, ailmentName, ailmentSymptomCount
FROM ailments a
LEFT JOIN(
SELECT FK_ailment_id, COUNT(FK_symptom_id) AS ailmentSymptomCount
FROM jnctn_ailments_symptoms GROUP BY FK_ailment_id
) temp
ON a.ailmentID = temp.FK_ailment_id
Demo
关于php - 多对多关系MYSQL-根据用户输入选择数据,例如症状,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/42793713/