mysql 如何搜索10个条件里面满足两个及以上的行？

Question

有一个tag表:

id	tag
1	a,b,c
2	a,c,d
3	a
4	e,g,z
5	a,y,z

请问如何查询 tag 里满足包含[a,c,e,f,g] 里面任意两个以上的？

也就是匹配到a,c的第一行第二行，匹配到e,g的第四行

Answer 1

初始化：

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');

查询：

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;

返回

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