解决的问题看截图,这个表里面的rid是逗号隔开的,需要查询rid的任何一个值在 (1,2,3)内
测试数据
CREATE TABLE `test` (
`rid` varchar(1024) DEFAULT NULL,
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1,2', '1');
INSERT INTO `test` VALUES ('2', '2');
INSERT INTO `test` VALUES ('3', '3');
INSERT INTO `test` VALUES ('4,5,6', '4');
INSERT INTO `test` VALUES ('2,3', '5');
INSERT INTO `test` VALUES ('1,2', '6');
INSERT INTO `test` VALUES ('3,4', '7');
查询sql
SELECT
*
FROM
(
SELECT
t.rid rid,
(
LENGTH(t.rid) - LENGTH(REPLACE(t.rid, ',', '')) + 1
) len
FROM
test t
) m
where
case m.rid
when 1
then m.rid in(1,2,3)
when 2
then (
substring_index(m.rid,',',1) in (1,2,3)
or REPLACE(REPLACE(substring_index(m.rid,',',2),substring_index(m.rid,',',1),''),',','') in (1,2,3)
)
when 3
then (
substring_index(m.rid,',',1) in (1,2,3)
or REPLACE(REPLACE(substring_index(m.rid,',',2),substring_index(m.rid,',',1),''),',','') in (1,2,3)
or REPLACE(REPLACE(substring_index(m.rid,',',3),substring_index(m.rid,',',3),''),',','') in (1,2,3)
)
when 4
then (
substring_index(m.rid,',',1) in (1,2,3)
or REPLACE(REPLACE(substring_index(m.rid,',',2),substring_index(m.rid,',',1),''),',','') in (1,2,3)
or REPLACE(REPLACE(substring_index(m.rid,',',3),substring_index(m.rid,',',3),''),',','') in (1,2,3)
or REPLACE(REPLACE(substring_index(m.rid,',',4),substring_index(m.rid,',',4),''),',','') in (1,2,3)
)
else false
end