SQL——根据字段包含值，统计条数（全文索引、CONTAINS、instr）

根据字段包含值，统计条数

例如my_column字段值可能为：“0,1,2,3,4,5,…”
目标，统计my_column中的值为0，为1，为2…的各个条数

方式一、使用CONTAINS

该功能建立在全文索引之上。

--删除全文索引
DROP CONTEXT INDEX IF EXISTS INDEX_STATISTICAL ON MY_DEV.my_table;
--创建全文索引，并增量填充。用于***情况统计
CREATE CONTEXT INDEX INDEX_STATISTICAL ON MY_DEV.my_table(my_column) LEXER ENGLISH_LEXER SYNC TRANSACTION;

--统计
select 
sum(case when is_del = 0 then 1 else 0 end) as personTotal,
sum(case when CONTAINS(my_column,'0') then 1 else 0 end) as democratic,
sum(case when CONTAINS(my_column,'1') then 1 else 0 end) as nonparty,
sum(case when CONTAINS(my_column,'2') then 1 else 0 end) as nonpartyIntellectuals,
sum(case when CONTAINS(my_column,'3') then 1 else 0 end) as minority,
sum(case when CONTAINS(my_column,'4') then 1 else 0 end) as religiousStaff,
sum(case when CONTAINS(my_column,'5') then 1 else 0 end) as overseasStudents,
sum(case when CONTAINS(my_column,'6') then 1 else 0 end) as hk,
sum(case when CONTAINS(my_column,'7') then 1 else 0 end) as macao,
sum(case when CONTAINS(my_column,'8') then 1 else 0 end) as taiwan,
sum(case when CONTAINS(my_column,'9') then 1 else 0 end) as taiwanFamily,
sum(case when CONTAINS(my_column,'10') then 1 else 0 end) as overseasChinese,
sum(case when CONTAINS(my_column,'11') then 1 else 0 end) as returnedOverseasChinese,
sum(case when CONTAINS(my_column,'12') then 1 else 0 end) as dependentsOverseasChinese
from MY_DEV.my_table
where is_del = 0;

其中，分词参数如下：

• CHINESE_LEXER，中文最少分词；
• CHINESE_VGRAM_LEXER，机械双字分词；
• CHINESE_FP_LEXER，中文最多分词；
• ENGLISH_LEXER，英文分词；
• DEFAULT_LEXER，中英文最少分词，也是默认分词；

方式二、使用instr

select sum(case when instr(my_column,'123')>0 then 1 else 0 end) as ca from My_DEV.my_table;

👉全文索引，友情参考链接：（全文索引）https://blog.csdn.net/Today_He/article/details/130198030

---

---

---