在 MySQL 中按特定关键字对结果进行分组？

Question

我有一个页面标记有多个标签，其中包含我正在搜索的关键字，有时它没有标记该关键字，因此当它具有该标签时，它将返回如下结果，

查询，

SELECT*
FROM root_pages AS p

LEFT JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

LEFT JOIN root_tags AS t
ON t.tag_id =  mm.tag_id
AND t.tag_name LIKE '%story%'

WHERE p.page_title LIKE '%article title 8%'
AND p.page_hide != '1'

ORDER BY (t.tag_name+0) ASC

结果，

page_id     page_url            tag_name    
17          article title 8     NULL
17          article title 8     NULL
17          article title 8     sys-rsv-story-1

所以我必须使用 < code>GROUP BY 来解决这个问题，

SELECT*
FROM root_pages AS p

LEFT JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

LEFT JOIN root_tags AS t
ON t.tag_id =  mm.tag_id
AND t.tag_name LIKE '%story%'

WHERE p.page_title LIKE '%article title 8%'
AND p.page_hide != '1'

GROUP BY p.page_id
ORDER BY (t.tag_name+0) ASC

它返回类似这样的东西，

page_id     page_url            tag_name    
17          article title 8     NULL

但是我在这个结果之后，它有我正在搜索的关键字，

page_id     page_url            tag_name    
17          article title 8     sys-rsv-story-1

那么，是否有可能按关键字对结果进行分组？或者其他更好的查询来存档这个？

另外，如果该关键字不存在，它不应该返回结果，但它仍然存在，

page_id     page_url            tag_name    
    17          article title 8     NULL
    17          article title 8     NULL

编辑：

我的新解决方案，

 SELECT*
FROM root_pages AS p

INNER JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

INNER JOIN root_tags AS t
ON t.tag_id =  mm.tag_id

WHERE p.page_title LIKE '%{group1}%'
AND t.tag_name LIKE '%story%'
AND p.page_hide != '1'

AND EXISTS (
    SELECT page_url
    FROM root_pages AS p

    LEFT JOIN root_mm_pages_tags AS mm
    ON mm.page_id = p.page_id

    LEFT JOIN root_tags AS t
    ON t.tag_id =  mm.tag_id

    WHERE page_url = 'article title 1d'
    AND t.tag_name LIKE '%story%'
    AND p.page_hide != '1'
)

ORDER BY (t.tag_name+0) ASC

Answer 1

尝试不在 LEFT JOIN 中使用条件：

SELECT *
FROM root_pages AS p

LEFT JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

LEFT JOIN root_tags AS t
ON t.tag_id =  mm.tag_id

WHERE p.page_title LIKE '%article title 8%'
AND p.page_hide != '1'
AND t.tag_name LIKE '%story%'

GROUP BY p.page_id
ORDER BY (t.tag_name+0) ASC

编辑： 如果您想获取页面标题包含“文章标题”的行以及没有该标题但具有所需关键字的行，请使用此查询（如 @用户985935建议）：

SELECT *
FROM root_pages AS p

LEFT JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

LEFT JOIN root_tags AS t
ON t.tag_id =  mm.tag_id

WHERE (p.page_title LIKE '%article title 8%'
OR t.tag_name LIKE '%story%')
AND p.page_hide != '1'


GROUP BY p.page_id
ORDER BY (t.tag_name+0) ASC

Answer 2

欧查。

我认为你的 SQL 查询很奇怪。

需要注意的几点：

• 使用 bar LIKE '%foo%' 对于 SQL 引擎来说非常困难，他必须顺序扫描所有行并在 bar 列中搜索子字符串 'foo'。索引使用不可用。因此，如果可以的话，请避免它。如果可以的话，至少使用 bar LIKE 'foo%' （如果有开头，则可以使用索引）。在您的情况下，您可能拥有标题为“文章标题 80”匹配的页面，您确定您不仅仅需要 p.page_title = '文章标题 8' 吗？
• 为什么要在 order by 指令中加上 +0 ？您真的想阻止索引的使用吗？
• p.page_hide != '1', p.page_hide 不是一个tinyint？它是一个字符串？为什么使用UTF8编码的字符来存储0或1？

但这不是问题。

您的问题之一是，使用 GROUP BY p.page_id 进行分组实际上在 SQL 中是错误的，但 MySQL 隐藏了这一事实。 group by 指令应至少包含 SELECT 部分中不是聚合的每个元素（聚合是计数或总和或平均值等）。这里你按 id 分组，你得到一个随机的东西，MySQL 认为你知道你在做什么，并且当 id 相同时，你确定 select 中的每个其他字段都是相同的（情况并非如此，tag_name不同）。

如果您有多个标签与您的关键字（此处为“故事”）匹配，您是否不希望该页面被多次列出？与所有标签？

所以。

您想要选择一个带有标签的页面。我想说使用 EXISTS 关键字可以让事情变得更简单。

可能是这样的：

SELECT * 
 FROM root_pages AS p
WHERE p.page_title = 'article title 8'
 AND p.page_hide != 1
 -- exists will return true as soon as the engine find one matching row
 AND EXISTS (
  SELECT mm.page_id
  FROM root_mm_pages_tags AS mm
    LEFT JOIN root_tags AS t
      ON t.tag_id =  mm.tag_id
  -- here we make a correlation between the subquery and the main query
  WHERE mm.page_id = p.page_id
  AND t.tag_name LIKE '%story%'
)

但是通过这个查询，您只获得页面名称，而不是标签结果。如果您想列出页面的所有匹配标签，则需要另一个查询，与您所拥有的非常接近：

SELECT p.page_id, p.page_name, t.tag_name
 FROM root_pages AS p
   INNER JOIN root_mm_pages_tags AS mm
       ON mm.page_id = p.page_id
     INNER JOIN root_tags AS t
         ON (t.tag_id =  mm.tag_id 
         AND t.tag_name LIKE '%story%')
WHERE p.page_title = 'article title 8'
 AND p.page_hide != 1

使用第一个 INNER JOIN 我只保留具有标签的页面。对于第二个 INNER JOIN，我只保留 root_mm_pages 中在 root_tags 中具有匹配标记的行。我认为您的 NULL 来自此表中链接到其他不匹配标签的行（因此在 root_tags 表结果中有 NULL 字段供您查询）。 因此，如果您只想匹配结果，请不要使用 LEFT JOIN。

如果您只想为每个表提供一个结果，则需要 GROUP BY p.page_id, p.page_name 并且需要在剩余字段 t.tag_name 上添加聚合函数代码>.您可以使用 GROUP_CONTACT(t.tag_name ORDER BY t.tag_name ASC SEPARATOR ",") 获取此表的所有匹配标签的列表。

编辑

因此，实际上您似乎想要具有匹配标题的页面OR具有匹配关键字的页面。在这种情况下，您应该使用LEFT JOIN，并且您将得到 NULL 值。如果您不需要结果中的标签，则 EXISTS 关键字仍然是您最好的朋友，只需将 AND EXISTS 替换为 OR EXISTS 即可。这是最快的解决方案。

如果您需要结果中的匹配标签，或者当它们没有标签时需要 NULL，那么您有 2 个解决方案。 UNION 查询混合了对标题的简单查询和对具有内连接的标签的查询的结果，或者使用 GROUP_CONCAT 进行良好的分组。如果您不使用 GROUP_CONCAT （如 @Dmitry Teplyakov 答案中所示），您可能会获得页面标题不匹配的结果，只有关键字匹配，但 tag_name 字段将显示 NULL 作为应用 GROUP BY 之前列出的第一个 tag_row查询上是一个NULL字段——该页面有3个关键字，匹配的关键字不是查询中的第一个——。

SELECT 
 p.page_id,
 p.page_name,
 GROUP_CONCAT(t.tag_name ORDER BY t.tag_name ASC SEPARATOR ",")
FROM root_pages AS p
   LEFT JOIN root_mm_pages_tags AS mm
       ON mm.page_id = p.page_id
     LEFT JOIN root_tags AS t
         ON t.tag_id =  mm.tag_id 
WHERE p.page_hide != 1
 AND (p.page_title = 'article title 8'
  OR t.tag_name LIKE '%story%')
GROUP BY p.page_id, p.page_name;

但在这里我们通过 tag_name 取消了您的订单。按 tag_name 排序意味着如果同一页面与关键字匹配多次，您希望同一页面出现在多行中。或者，如果名称匹配并且关键字也匹配......或者可能不匹配。所以实际上 UNION 查询解决方案可能更好。但关键是你应该在 tag_name 字段中解释你想要什么:-)

Answer 3

这是我在评论中提到的示例查询：

SELECT *
FROM root_pages AS p

LEFT JOIN root_mm_pages_tags AS mm
ON mm.page_id = p.page_id

LEFT JOIN root_tags AS t
ON t.tag_id =  mm.tag_id

WHERE p.page_hide != '1'
AND (t.tag_name LIKE '%story%' OR p.page_title LIKE '%article title 8%')
GROUP BY p.page_id
ORDER BY (t.tag_name+0) ASC