mysql - 我如何构建程序来返回单词在sql中每行出现的次数

Question

例子带列文本的表名称(全文)

id text
1   Lorem Ipsum has **Dani** been the industry's standard dummy
2   scrambled it to **Mike** make a type specimen book
3   popular belief, Lorem Ipsum is not **John** simply random text
4   popular **Dani** random text
5   popular belief, Lorem **Dani** simply random text 
6   popular belief, Lorem **Dani** simply random text simply text **Dani** random text
7   scram **Mike** make a type specimen book tess test

我搜索的词(Dani、Mike、John)我想构建返回的程序

text_search number_time_show_in_each_row 
Dani           4
Mike           2
John           1 

Answer 1

我相信这就是你正在寻找的东西注意:结果应该给出 5 Dani，因为 id 1 有 1，id 4 有 1，id 5 有 1，id 6 有 2。

text_search number_time_show_in_each_row 
Dani           5
Mike           2
John           1 

如果您使用的是 sql server，您可以使用 len 来表示长度，如果是 postgres，则使用 length 而不是 len。 Oracle 也使用长度。因此，根据您的 sql 平台，您只需将单词 len 更改为相应的单词，下面的查询就会起作用

;with cte as
(
select 1 AS id  ,'Lorem Ipsum has **Dani** been the industry''s standard dummy' as text
union select 2   ,'scrambled it to **Mike** make a type specimen book'
union select 3   ,'popular belief, Lorem Ipsum is not **John** simply random text'
union select 4  ,'popular **Dani** random text'
union select 5   ,'popular belief, Lorem **Dani** simply random text' 
union select 6   ,'popular belief, Lorem **Dani** simply random text simply text **Dani** random text'
union select 7   ,'scram **Mike** make a type specimen book tess test'
)
select text_search, sum(OCcurrence) as number_time_show_in_each_row 
from
(
    select 'Dani' as text_search  ,(len(text)-len(replace(text, 'Dani','')))/len('Dani') as OCcurrence from cte
    union all
    select 'Mike',(len(text)-len(replace(text, 'Mike','')))/len('Mike') from cte
    union all
    select 'John',(len(text)-len(replace(text, 'John','')))/len('John') from cte
) a group by text_search

下面是一个包含存储过程的解决方案

create table mytable(id int, text text);
insert into mytable ( id, text)
select id, text from
(
select 1 AS id  ,'Lorem Ipsum has **Dani** been the industry''s standard dummy' as text
union select 2   ,'scrambled it to **Mike** make a type specimen book'
union select 3   ,'popular belief, Lorem Ipsum is not **John** simply random text'
union select 4  ,'popular **Dani** random text'
union select 5   ,'popular belief, Lorem **Dani** simply random text' 
union select 6   ,'popular belief, Lorem **Dani** simply random text simply text **Dani** random text'
union select 7   ,'scram **Mike** make a type specimen book tess test'
) a;

CREATE PROCEDURE GetAllOccurrence()
BEGIN
    select text_search, sum(Occurrence) as number_time_show_in_each_row 
    from
    (
        select 'Dani' as text_search  ,(length(text)-length(replace(text, 'Dani','')))/length('Dani') as OCcurrence from mytable
        union all
        select 'Mike',(length(text)-length(replace(text, 'Mike','')))/length('Mike') from mytable
        union all
        select 'John',(length(text)-length(replace(text, 'John','')))/length('John') from mytable
    ) a group by text_search;
END;