按出现次数排序的MySQL

Question

按出现次数排序的MySQL

6

我正在两个文本字段中称为“Subject”和“Text”搜索特定关键字。为此，我使用了“LIKE”语句。当尝试按出现次数对结果进行排序时，遇到了问题。

我的搜索查询如下：

SELECT * FROM Table WHERE (Text LIKE '%Keyword%' OR Subject LIKE '%Keyword%')

我尝试添加一个count()语句，并按出现次数排序，但count()语句只返回表中的行数。

以下是带有count语句的查询：

SELECT *, COUNT(Text LIKE '%Keyword%') AS cnt FROM News WHERE (Text LIKE '%Keyword%' OR Subject LIKE '%Keyword%') ORDER BY cnt

我要找的是能够返回每行主题和文本列上匹配数量的内容，并按照每行关键字出现次数最高的顺序对结果进行排序的东西。

- Marcus Hanikat

您想按照文本中关键词出现的次数或表格中关键词出现的次数进行排序吗？ - CodeBird

你可以使用在这里提到的 substr_count MySQL 函数（http://stackoverflow.com/questions/19173251/mysql-native-similar-to-php-substr-count） - Jan Turoň

1

我认为全文搜索（https://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html）可能会对你有所帮助。 - LSerni

我尝试了使用全文搜索的方式来完成此任务，但似乎它会将存在于多行中的常见单词筛选出来。由于我的文本不是很长，有些关键词可能会出现在多行中。如果我正确理解全文搜索，则它会专注于每个行唯一的单词，因此我决定尝试寻找其他方法。这样做正确吗？还是我在尝试全文搜索时犯了什么错误？ - Marcus Hanikat

3个回答

2

你需要使用SUM而不是COUNT。COUNT函数将会计算非空记录的数量，这意味着所有匹配和不匹配的都会被计算在内。

SELECT *, SUM(Text LIKE '%Keyword') AS total_matches
...
ORDER BY total_matches

SUM()函数将计算LIKE操作产生的布尔真结果的数量，这些结果将被强制转换为整数，因此你会得到类似于1+1+1+0+1=4的结果，而不是5个非空计数。

- Marc B

1

我认为他想知道关键字在单个元组中出现的次数。SUM('So the quick brown fox jumped over the lazy dog' LIKE '%the%')返回1，我认为他希望得到一个返回2的结果。 - LSerni

是的，我试过这个了，正如Iserni所说，我正在寻找一些能够返回每个搜索行中出现次数并随后对它们进行排序的东西。我在问题中漏掉了那部分，我会修复它 :) - Marcus Hanikat

0

// escape $keyword for mysql
$keyword = strtolower('Keyword');
// now build the query
$query = <<<SQL
    SELECT *,
    ((LENGTH(`Subject`) - LENGTH(REPLACE(LOWER(`Subject`), '{$keyword}', ''))) / LENGTH('{$keyword}')) AS `CountInSubject`,
    ((LENGTH(`Text`) - LENGTH(REPLACE(LOWER(`Text`), '{$keyword}', ''))) / LENGTH('{$keyword}')) AS `CountInText`
    FROM `News`
    WHERE (`Text` LIKE '%{$keyword}%' OR `Subject` LIKE '%{$keyword}%')
    ORDER BY (`CountInSubject` + `CountInText`) DESC;
SQL;

返回每个字段中出现次数并按此排序。

为使其正常工作，'keyword' 需要小写化。我认为它在性能方面不是真正快速的，因为它需要将字段转换为小写，并且 MySQL 中没有大小写不敏感的搜索。

您可以通过单词对每个 news 项目 （subject 和 text） 进行索引，并将其存储在另一个带有 news_id 和出现次数的表中，然后与之匹配。

- CodeAngry

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- M Khalid Junaid · Accepted Answer

以下查询可以给出字符串在文本和主题两列中出现的次数，并按标准对结果进行排序，但从性能方面来说，这不是一个好的解决方案，最好在应用程序代码级别对结果进行排序。

SELECT *,
(LENGTH(`Text`) - LENGTH(REPLACE(`Text`, 'Keyword', ''))) / LENGTH('Keyword')
+
(LENGTH(`Subject`) - LENGTH(REPLACE(`Subject`, 'Keyword', ''))) / LENGTH('Keyword') `occurences`
 FROM 
`Table`
 WHERE (Text LIKE '%Keyword%' OR Subject LIKE '%Keyword%')
ORDER BY `occurences`  DESC

Fiddle演示

建议由@lserni提出一种更清晰的计算发生次数的方法。

SELECT *,
(LENGTH(`Text`) - LENGTH(REPLACE(`Text`, 'test', ''))) / LENGTH('test') `appears_in_text`,

(LENGTH(`Subject`) - LENGTH(REPLACE(`Subject`, 'test', ''))) / LENGTH('test') `appears_in_subject`,

(LENGTH(CONCAT(`Text`,' ',`Subject`)) - LENGTH(REPLACE(CONCAT(`Text`,' ',`Subject`), 'test', ''))) / LENGTH('test') `occurences`
 FROM 
`Table1`
 WHERE (TEXT LIKE '%test%' OR SUBJECT LIKE '%test%')
ORDER BY `occurences`  DESC

按出现次数排序的MySQL

Fiddle演示

Fiddle Demo 2