Comment compter les occurrences de sous-chaînes et les résultats de commande par fréquence dans 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 |

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!