如何在 MySQL 中統計子字串的出現次數並按頻率對結果進行排序？

Counting Substring Instances and Ordering Results in MySQL

In MySQL, you can count the occurrences of a substring within a string field and sort the results based on the frequency of those occurrences using the following query:

SELECT (CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str, substr, ''))) / CHAR_LENGTH(substr) AS cnt
...
ORDER BY cnt DESC

This expression calculates the count of substrings by dividing the difference between the length of the original string and the length of the string with the substring replaced with an empty string by the length of the substring. The results are then sorted in descending order of the count.

Example:

Consider a table with a column host that contains the following values:

'127.0.0.1'
'honeypot'
'honeypot'
'localhost'
'localhost'

To count the occurrences of the substring 'l' in the host column and order the results by the count, you would use the following query:

SELECT host, (CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, 'l', ''))) / CHAR_LENGTH('l') AS cnt
FROM user
ORDER BY cnt DESC

The results would be:

| host      | cnt    |
|-----------+--------|
| localhost | 2.0000 |
| localhost | 2.0000 |
| honeypot  | 0.0000 |
| honeypot  | 0.0000 |
| 127.0.0.1 | 0.0000 |

以上是如何在 MySQL 中統計子字串的出現次數並按頻率對結果進行排序？的詳細內容。更多資訊請關注PHP中文網其他相關文章！