Mysql计数子字符串的实例，然后排序 [英] Mysql count instances of substring, then order by

问题描述

我的mySQL中有一个问题，如下所示：

I have a problem in mySQL that goes as follows:

• 在mySQL数据库的字符串字段中计算子字符串的实例


• 按照该子串（DESC）的出现次数对结果排序

推荐答案

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

是的，看起来blo肿，但afaik没有任何其他可能的解决方案。

Yep, looks bloated but afaik there is no any other possible solution.

mysql> select (CHAR_LENGTH('asd') - CHAR_LENGTH(REPLACE('asd', 's', ''))) / CHAR_LENGTH('s');
+-----------------------------------------------------------------+
| (CHAR_LENGTH('asd') - CHAR_LENGTH(REPLACE('asd', 's', ''))) / CHAR_LENGTH('s') |
+-----------------------------------------------------------------+
|                                                          1.0000 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> select host, (CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, 'l', ''))) / CHAR_LENGTH('l') AS cnt from user;
+-----------+--------+
| host      | cnt    |
+-----------+--------+
| 127.0.0.1 | 0.0000 |
| honeypot  | 0.0000 |
| honeypot  | 0.0000 |
| localhost | 2.0000 |
| localhost | 2.0000 |
+-----------+--------+
5 rows in set (0.00 sec)

这篇关于Mysql计数子字符串的实例，然后排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！