mysql 出现次数排序_MySQL按出现次数排序

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

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