表一结构
-- ----------------------------
-- Table structure for test1
-- ----------------------------
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
`t1_id` int(11) NOT NULL AUTO_INCREMENT,
`t1_name` varchar(255) DEFAULT NULL,
`t2_id` varchar(30) DEFAULT NULL,
PRIMARY KEY (`t1_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of test1
-- ----------------------------
INSERT INTO `test1` VALUES ('1', 'dj', '1,2');
INSERT INTO `test1` VALUES ('2', 'zf', '2,3');
表二结构
-- ----------------------------
-- Table structure for test2
-- ----------------------------
DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
`t2_id` int(11) NOT NULL AUTO_INCREMENT,
`t2_value` varchar(255) DEFAULT NULL,
PRIMARY KEY (`t2_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of test2
-- ----------------------------
INSERT INTO `test2` VALUES ('1', 'value1');
INSERT INTO `test2` VALUES ('2', 'value2');
INSERT INTO `test2` VALUES ('3', 'value3');
INSERT INTO `test2` VALUES ('4', 'value4');
sql语句
select ta.t1_id,GROUP_CONCAT(ta.t2_value) from
(
select t1.t1_id,t1.t1_name,t1.t2_id t2id,t2.t2_id,t2.t2_value,FIND_IN_SET(t2.t2_id,t1.t2_id) tt from test1 t1,test2 t2
) ta
where tt>0
GROUP BY ta.t1_id
结果
1 value1,value2
2 value2,value3