我有一个包含列symptom_ratings
、id
、user_id
、review_id
、symptom_id
、rate
和strain_id
的表。
每个回顾可以在symptom_ratings
中有多个条目,每个症状一个条目。
我想对每个strain_id
进行搜索,其中包含用户搜索的所有symptom_id
。
即,给定列:
review: 2, strain_id: 3, symptom_id: 43
review: 2, strain_id: 3, symptom_id: 23
review: 2, strain_id: 3, symptom_id: 12
review: 6, strain_id: 1, symptom_id: 3
review: 6, strain_id: 2, symptom_id: 12
搜索
symptom_id
的43和12只应返回strain_id
3的结果。我目前使用以下
WHERE
条件:Strain.id IN (SELECT strain_id
FROM symptom_ratings
WHERE symptom_id
IN ($symptoms))
其中
$symptoms
是以逗号分隔的symptom_id
值列表。我的问题是,这个查询当前执行一个或搜索(即,它会找到有任何症状的菌株),而我更喜欢使用一个和搜索(即,找到有所有症状的菌株)。我怎样才能做到?
最佳答案
一种方法是按菌株ID对行进行分组,计算每组中不同匹配症状的数量,并仅返回那些计数等于搜索到的症状总数的行:
SELECT
strain_id,
COUNT(DISTINCT symptom_id) AS matched_symptoms
FROM symptom_ratings
WHERE symptom_id IN (43, 12)
GROUP BY strain_id
HAVING matched_symptoms = 2
Here's a quick online demo.
此方法的一个潜在有用特性是,只需更改
HAVING
子句中的条件,就可以轻松地将其扩展为同时支持“所有这些”、“任何这些”和“至少n个这些”搜索。对于后一种情况,您还可以根据匹配症状的数量对结果进行排序(例如,使用ORDER BY matched_symptoms DESC
)。