mySql:获取单元格中的单词数(以逗号分隔的字符串)与匹配单元格具有相同值的行 [英] mySql: gets the rows where the number of words in the cell (string comma separated) has the same value as a matched cell

问题描述

我有一个这样的表:

CREATE TABLE tab1 ( 
  id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, 
  cod TEXT,
  type TEXT,
  qta INTEGER);

INSERT INTO tab1 (cod, type, qta) 
  VALUES ('aaa','aaa,bbb,ccc', 3),
          ('aaa','ddd', 1),
          ('aaa','eee,fff', 4),
          ('aaa','ggg,hhh', 2),
          ('aaa','out', 7),
          ('aaa','out', 7);

我想知道类型"列的每个单元格中有多少个单词.

I would like to know how many words there are in each cell of column 'type'.

对我来说最好的是只保留单词数量与'qta'相同的行.

The best for me is to have only the rows that the number of words are the same of 'qta'.

所以我只想要ID为1、2、4的行

So I would like to have only the rows with id 1, 2, 4

代码的原始链接:此处

推荐答案

考虑:

select *
from tab1
where char_length(type) - char_length(replace(type, ',', '')) + 1 = qta

表达式char_length(type) - char_length(replace(type, ',', ''))为您提供字符串中的逗号数.在其中添加1可以得到字符串中的单词数.

Expression char_length(type) - char_length(replace(type, ',', '')) gives you the number of commas in the string. Adding 1 to that gives you the number of words in the string.

DB Fiddle上的演示 :

Demo on DB Fiddle:

id | cod | type        | qta
-: | :-- | :---------- | --:
 1 | aaa | aaa,bbb,ccc |   3
 2 | aaa | ddd         |   1
 4 | aaa | ggg,hhh     |   2

这篇关于mySql:获取单元格中的单词数(以逗号分隔的字符串)与匹配单元格具有相同值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！