mysql - 多个答案与多个值的比较

Question

假设我们有一个包含以下数据的模型Answer:

pic_id   | gender | is_nude | has_advertisement
2530     | female | 0       | 1
2530     | male   | 1       | 0
2530     | male   | 1       | 0
2530     | male   | 0       | 1
2530     | male   | 1       | 0

如何在所有字段(gender、is_nude 和 has_advertisement)获得 3 个一致的答案或 nil 如果少了？

Answer 1

这是一个聚合查询。以下为您提供所有“组”答案及其计数:

select gender, is_nude, has_advertisement, count(*) as NumAnswers
from Answers
group by gender, is_nude, has_advertisement

如果您只想要恰好 3 个匹配项，则添加子句:

having NumAnswers = 3

如果您想要最大的一组，请添加:

order by NumAnswers desc
limit 1