我有一个高级搜索表单，它提供了多种方法来过滤您的搜索。这是一个简化的想法(不包括关键字文本输入或日期范围搜索或其他选择菜单):

Topic: 
<select><option>any</option><option>all</option></select>
[] Aging
[] Environment
[] Health
[] Hunger
[] Poverty

Document type: 
<select><option>any</option><option>all</option></select>
[] Case Study
[] Policy Brief
[] Whitepaper

如果某人在选择多个主题或文档类型时选择了“任何”，则查询需要包括，例如，topic = "Aging"OR topic = "Health"。

如果某人在选择多个主题或文档类型时选择了“全部”，则查询需要包括，例如，topic = "Aging"AND topic = "Health"。

我们默认在这些不同的过滤器之间使用“AND”。因此，当您搜索分类为 Aging 的所有文档和分类为白皮书的所有文档时，查询为:topic = "Aging"AND doctype = "whitepaper"。

问题:当搜索“any”时，我们有一个有效的查询。但是当搜索“all”时，根据 MySQL 的“EXPLAIN”命令，我们有一个“不可能的 WHERE”。 :(

这是当有人为主题和文档类型都选择“任意”时有效的查询:

SELECT 
DISTINCT * 
FROM research 
JOIN link_resource_doctype ON link_resource_doctype.resource_id = research.research_id 
JOIN doctype ON doctype.id = link_resource_doctype.doctype_id 
JOIN link_resource_issue_area ON link_resource_issue_area.resource_id = research.research_id 
JOIN issue_area ON issue_area.id = link_resource_issue_area.issue_area_id 
WHERE approved = '1' 
AND (doctype.identifier = 'case_study' OR doctype.identifier = 'whitepaper') 
AND (issue_area.identifier = 'aging' OR issue_area.identifier = 'health')

当有人为主题和文档类型选择“全部”时，这里是相同的查询不起作用(如果有人只选择主题或文档类型，这也不起作用):

SELECT 
DISTINCT * 
FROM research 
JOIN link_resource_doctype ON link_resource_doctype.resource_id = research.research_id 
JOIN doctype ON doctype.id = link_resource_doctype.doctype_id 
JOIN link_resource_issue_area ON link_resource_issue_area.resource_id = research.research_id 
JOIN issue_area ON issue_area.id = link_resource_issue_area.issue_area_id 
WHERE approved = '1' 
AND (doctype.identifier = 'case_study' AND doctype.identifier = 'whitepaper') 
AND (issue_area.identifier = 'aging' AND issue_area.identifier = 'health')

可能的解决方案，但有一个问题: 我在 Stackoverflow 上看到这篇文章 -- Select row belonging to multiple categories -- 其中包含一个查询，我认为当有人选择“全部”时，它可能会解决我们的问题。在这里:

SELECT 
DISTINCT * 
FROM research 
JOIN link_issue_area ON link_issue_area.resource_id = research.research_id 
JOIN link_doctype ON link_doctype.resource_id = research.research_id 
WHERE issue_area.identifier IN ('aging', 'health')
AND 
doctype_id.identifier IN ('case_study', 'whitepaper')
GROUP BY research.research_id
HAVING COUNT(DISTINCT issue_area.identifier) = 2 
AND 
COUNT(DISTINCT doctype.identifier) = 2

问题:除了一个问题外，这个查询似乎对“任何”或“所有”都有效。假设一份文件被归类为“老龄化”、“健康”和“贫困”，但搜索者只勾选了“老龄化”和“健康”。分类在已勾选的两个主题和未勾选的 Poverty 下的文档将不会出现在搜索结果列表中。我认为这是因为 HAVING COUNT (DISTINCT issue_area.identifier) = 2 —— 2 排除了任何实际 COUNT 大于 2 的文档。是否有解决方法？或者在这里使用更好的查询？

非常感谢任何见解、想法和帮助!谢谢!

这里有一个 SQLfiddle，它也包含所有这些:http://sqlfiddle.com/#!2/847362/1

最佳答案

我不太明白这个问题，因为你没有显示预期的输出。但据我所知，这就是到目前为止我所做的。请评论错误是什么:

可能的解决方案，但可能有效:

SELECT   research.research_id AS resource_id, research.title
FROM     research
JOIN     link_issue_area ON link_issue_area.resource_id = research.research_id
JOIN     link_doctype ON link_doctype.resource_id = research.research_id
JOIN (SELECT resource_id, COUNT(DISTINCT issue_area_id) AS ISSUE_COUNT FROM link_issue_area
      GROUP BY resource_id) TB1_COUNT ON TB1_COUNT.resource_id = research.research_id
JOIN (SELECT resource_id, COUNT(DISTINCT doctype_id) AS DOCTYPE_COUNT FROM link_doctype
     GROUP BY resource_id) TB2_COUNT ON TB2_COUNT.resource_id = research.research_id
WHERE    issue_area_id IN (5,10)
AND       doctype_id IN (3,18)
AND   TB1_COUNT.ISSUE_COUNT = 2
AND TB2_COUNT.DOCTYPE_COUNT = 2
GROUP BY resource_id
LIMIT 0,1

这是 SQLFiddle

关于使用 HAVING 的 MySQL 查询错误地限制了结果，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/18624425/