DWQA QuestionsCategory: DatabaseHow does MySQL search for two or more lines in 10 conditions?
The best food in the station asked 4 weeks ago

There is onetagTable:

id
tag

1
a,b,c

2
a,c,d

3
a

4
e,g,z

5
a,y,z

How to query the content of a tag[a,c,e,f,g]More than two of them?
That is, matching toa,cFirst line, second line, match toe,gThe fourth line of

1 Answers
Yujiaao answered 4 weeks ago

initialization:


CREATE TABLE `tag` (
  `id` bigint(20) NOT NULL,
  `tag` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of tag
-- ----------------------------
INSERT INTO `tag` VALUES ('1', 'a,b,c');
INSERT INTO `tag` VALUES ('2', 'a,c,d');
INSERT INTO `tag` VALUES ('3', 'a');
INSERT INTO `tag` VALUES ('4', 'e,g,z');
INSERT INTO `tag` VALUES ('5', 'a,y,z');

Query:

SELECT id, tag from tag , (select 'a' as needle union select 'c' union select 'e' union select 'f' union select 'g' ) as t 
 where id>0 and FIND_IN_SET(t.needle, tag.tag)>0   group by id  having count(id)>=2;

return

1 a,b,c
2 a,c,d
4 e,g,z