擅长:python、mysql、java
<p>SQLite没有很好的字符串处理能力,也没有返回表的方法。但是,它确实支持递归CTE。您可以使用此结构将标题拆分为单词:</p>
<pre><code>with recursive cte as (
select null as word, title || ' ' as rest, 0 as lev
from t
union all
select substr(rest, 1, instr(rest, ' ') - 1) as word,
substr(rest, instr(rest, ' ') + 1) rest,
lev + 1
from cte
where lev < 5 and rest like '% %'
)
select word, count(*)
from cte
where word is not null
group by word;
</code></pre>
<p><a href="https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=fa4fa8591c7918a0a26c56fe00c9a9b0" rel="nofollow noreferrer">Here</a>是一个db<&燃气轮机;小提琴</p>
<p>要获取最上面的单词,您可以使用:</p>
<pre><code>select word, count(*)
from cte
where word is not null
group by word
order by count(*) desc
limit 1;
</code></pre>