获取字段中字符串中包含最多单词的行

Question

在PHP中我有一个字符串$str = 'word1 word2 word3'。

然后我有一个mysql-db-field叫做content。

我需要哪个mysql语句来获取最多的行str在其content中的词?

的例子:

• Row1字段:word1 word2 word1 word1 word1 word2
• Row2字段:word9 word2 word1 word8 word2
• 行字段3:word1 word2 word1 word1 word3

将输出Row3，因为它包含word1,word2和word3。

• Row1字段:word1 word2 word1 word1 word1 word2
• Row2字段:word9 word2 word2 word8 word2
• 行字段3:word1 word1 word1 word1 word1 word9

将输出Row1，因为它包含word1和word2。

Answer 1

你可以(如果你有MySQL8.0或更新):

SET @str = 'word1 word2 word3';
DROP  TABLE IF EXISTS content;
CREATE TABLE content(i integer, str VARCHAR(200));
INSERT INTO content VALUES
(1,'word1 word2 word1 word1 word2'),
(2,'word9 word2 word1 word8 word2'),
(3,'word1 word2 word1 word1 word3');
WITH RECURSIVE abc AS (
SELECT 
LEFT(@str,instr(@str,' ')-1) as a, 
MID(CONCAT(@str,' '),instr(@str,' ')+1) as b
UNION ALL
SELECT LEFT(b,instr(b,' ')-1), MID(b,instr(b,' ')+1)
FROM abc
WHERE rtrim(b)<>'')
SELECT
x.i,
content.str,
sum(c)
FROM (
SELECT 
a , 
content.str, 
content.i,
(length(content.str)-length(replace(content.str,a,'')))/length(a) as c
FROM abc
CROSS JOIN content) x
INNER JOIN content on content.i=x.i
GROUP BY x.i;

输出:

<表类>我str和(c)1word1 word1 word1 word1 word25.0002word9 word2 word1 word8 word23.00003word1 word2 word1 word1 word35.000