mysql - 限制带有子项的查询

Question

假设我有一个可以使用某些工具解决的谜题列表。现在我选择我的工具，我想知道我可以解决哪个难题以及使用列表中的哪个工具。这是我的sql(对于mysql)

 SELECT p.name puzzle, countr.cnt, tool.id toolId,[etc]
 FROM
puzzles p
 INNER JOIN
tools_for_puzzle tfp
 ON tfp.id_puzzle = p.id 
 INNER JOIN
tools t
 ON t.id= tfp.id_tool
INNER JOIN
(
   select tfp2.id_puzzle, count(*) cnt from tools_for puzzle tfp2
   where tfp2.id_tool in (21,22,23,24,25,26,27) group by id_puzzle
) countr
 ON
countr.id_puzzle=tfp.id_puzzle
 WHERE
t.id IN (21,22,23,24,25,26,27)
    order by cnt desc   limit 50 

结果我得到了类似的东西谜题1、工具1、2谜题1、工具2、2谜题2、工具1、1谜题3、工具3、1

需要计数器来知道我可以更轻松地解决哪个谜题，因为我有更多工具。不过，我不确定查询是否足够优化。我得到的结果是 50 行，并不代表 50 个可能的谜题，而只是行的总和。假设我使用需要的 50 个工具的 ID，我只能得到一个谜题。

如何设置查询以获得 50 个不同的谜题而不是谜题、工具？如果我按 Puzzle 分组，我将丢失工具的 ID/名称!我应该运行第三个查询吗？

Answer 1

如果我理解的话，您正在寻找与一组给定工具相匹配的谜题。这是“集合内集合”子查询的示例。解决这个问题的一种灵活方法是使用聚合。

以下查询将返回每个谜题的工具数量及其列表(以逗号分隔)。对于与您的列表匹配的谜题，它按照工具从最多到最少的顺序排列:

SELECT p.name, p.puzzle, count(*) as numtools, group_concat(t.id) as tools
FROM puzzles p INNER JOIN
     tools_for_puzzle tfp
     ON tfp.id_puzzle = p.id INNER JOIN
     tools t
     ON t.id= tfp.id_tool
WHERE t.id IN (21,22,23,24,25,26,27)
GROUP BY p.id
ORDER BY numtools desc;