MySQL按出现次数排序 [英] MySQL sort by number of occurrences

问题描述

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

我的搜索查询如下所示：

  SELECT * FROM Table WHERE（Text LIKE'％Keyword％'OR Subject LIKE'％Keyword％'）
  

我试图添加一个count（）语句，并按出现次数排序，但count（）语句只是继续返回我的表中的行数。 / p>

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

  SELECT *，COUNT （文本LIKE'％关键字％'）AS cnt FROM新闻WHERE（文本LIKE'％关键字％'或主题LIKE'％关键字％'）ORDER BY cnt 
  

查找的内容是返回每行主题和文本列上的匹配数，然后在关键字的最大出现次数后对结果排序每一行。

解决方案

下面的查询可以给你出现的字符串出现在两列，即文本和主题，按照标准排序结果，但这不会是一个良好的解决方案性能明智地，它更好地排序应用程序代码级别的结果

  SELECT *，
（LENGTH（`Text`） -  LENGTH（REPLACE（`Text`，'Keyword'，''））/ LENGTH（'Keyword'）
 + 
 （`Subject`） -  LENGTH（REPLACE（`Subject`，'Keyword'，''））/ LENGTH（'Keyword'）`occurences'
 FROM 
```
 WHERE （文本LIKE'％关键字％'或主题LIKE'％关键字％'）
 ORDER BY`ORESER'DESC 
  

b $ b

小提示演示

建议 @lserni 更清晰的计算方式

  SELECT *，
（LENGTH（`Text`） -  LENGTH（REPLACE（`Text`，'test'，''）））/ LENGTH `appear_in_text`，
 
（LENGTH（`Subject`） -  LENGTH（REPLACE（`Subject`，'test'，''）））/ LENGTH（'test'）`appear_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 
  

Fiddle Demo 2

I am doing a search in two text fields called Subject and Text for a specific keyword. To do this I use the LIKE statement. I have encountered a problem when trying to sort the results by the number of occurrences.

my search query looks like this:

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

I tried to add a count() statement and sort it by the number of occurrences, but the count() statement just keep returning the number of rows in my table.

Here is the query with count statement:

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

What im looking for is something that returns the number of matches on the Subject and Text columns on each row, and then order the result after the highest amount of occurrences of the keyword on each row.

解决方案

Below query can give you the no.of occurrences of string appears in both columns i.e text and subject and will sort results by the criteria but this will not be a good solution performance wise its better to sort the results in your application code level

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 Demo

Suggested by @lserni a more cleaner way of calculation of occurrences

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

Fiddle Demo 2

这篇关于MySQL按出现次数排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！