Mysql 统计子串的实例，然后按顺序排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5427467/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 19:17:46 来源:igfitidea点击:
Mysql count instances of substring, then order by
提问by Alan
I have a problem in mySQL that goes as follows:
我在 mySQL 中有一个问题，如下所示：
- Count the instances of a substring in a string field in a mySQL database
- Order the results by the number of occurrences of that substring (DESC)
- 计算 mySQL 数据库中字符串字段中子字符串的实例数
- 按该子字符串的出现次数 (DESC) 对结果进行排序
I have never done anything other than rudimentary queries.. I can't find a solution elsewhere.
除了基本的查询之外，我从未做过任何事情......我在其他地方找不到解决方案。
回答by zerkms
SELECT (CHAR_LENGTH(str) - CHAR_LENGTH(REPLACE(str, substr, ''))) / CHAR_LENGTH(substr) AS cnt
...
ORDER BY cnt DESC
Yep, looks bloated but afaik there is no any other possible solution.
是的，看起来很臃肿，但 afaik 没有任何其他可能的解决方案。
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)
回答by FedeEDavide
DELIMITER //
DROP FUNCTION IF EXISTS `subStringCount`//
CREATE FUNCTION `subStringCount` (sequence VARCHAR(1000), word VARCHAR(100)) RETURNS INT(4)
DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE counter SMALLINT UNSIGNED DEFAULT 0;
DECLARE word_length SMALLINT UNSIGNED;
SET word_length = CHAR_LENGTH(word);
WHILE (INSTR(sequence,word) != 0) DO
SET counter = counter+1;
SET sequence = SUBSTR(sequence, INSTR(sequence,word)+word_length);
END WHILE;
RETURN counter;
END //
DELIMITER ;
Which can be executed by calling:
可以通过调用执行：
SELECT sum(subStringCount(fieldName,'subString')) FROM `table` WHERE 1
