我有一个包含列symptom_ratingsiduser_idreview_idsymptom_idratestrain_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_id3的结果。
我目前使用以下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)。

10-07 19:33
查看更多