赞
踩
解决的问题看截图,这个表里面的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

Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。