Wie zähle ich das Vorkommen von Teilzeichenfolgen und ordne die Ergebnisse nach Häufigkeit in 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 |

Das obige ist der detaillierte Inhalt vonWie zähle ich das Vorkommen von Teilzeichenfolgen und ordne die Ergebnisse nach Häufigkeit in MySQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!