SQLITE 中是否有除"LIKE"之外的'CONTAINS'函数版本？

Question

我试图找到1到7范围内每个数字的总数。但数据中包含了这些数字的不同组合。例如1；2.3,7；1,2,3等等。我想找出每个数字弹出的总次数。我主要想要的是一个SQLite的代码，如下所示：

select <fields>, count(*)
from tablexyz
where <field> contains '2' (and '3','4',... individually)

当我输入"；其中类似于"2%"；因此，它只给出了所有以2开头的级数，但否定了以1开头但包含2的级数。

任何帮助都将不胜感激！

Answer 1

我想找到每个数字弹出的总次数

您的示例代码和您所说的解决方案并不完全一致。我能想到的最接近

with  t (txt) as -- a sample record from your table
(select '1; 2; 3,7; 1,2,3'),
t2 (num) as  -- a lookup table we can create for range of numbers 1-7
(select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7)
select t2.num, length(t.txt) - length(replace(t.txt,t2.num,'')) as num_occurence
from t2
left join t on t.txt like '%' || t2.num || '%'

输出

+-----+---------------+
| num | num_occurence |
+-----+---------------+
|   1 | 2             |
|   2 | 2             |
|   3 | 2             |
|   4 | NULL          |
|   5 | NULL          |
|   6 | NULL          |
|   7 | 1             |
+-----+---------------+

演示

Answer 2

使用下面的解决方案，您可以构建一个"表"；从数字1到7，然后将其连接到源数据表中，以计数该行中是否出现该数字，然后将它相加。

查询

WITH
sample_data (nums)
AS
(SELECT '1,2,3,4,5,6' 
UNION ALL
SELECT '3,4,5,6' 
UNION ALL
SELECT '1,2,7,6' 
UNION ALL
SELECT '6' ),
search_nums (search_num)
AS
(VALUES(1)
UNION ALL
SELECT search_num+1 FROM search_nums WHERE search_num<7)
select search_num, sum(count_of_num) from (
SELECT s.nums,
n.search_num,
case
instr(s.nums, n.search_num)
when 0 then 0
else 1
end as count_of_num
FROM sample_data  s, search_nums n
) group by search_num;

结果

search_num  sum(count_of_num)
1           2
2           2
3           2
4           2
5           2
6           4
7           1