Mysql在同一列中加入多个索引

Question

我的 MySQL 查询有问题，可能与连接有关。

我的数据库结构是这样的:

Table A
    id, nameA, nameB
Table B
    some_stuff, ids

ids 在第二个表中是一个字符串，包含一个或多个来自第一个表的id

我想从两行中获取数据，比如:

Result
    some_stuff, ids, firstId, firstNameA, firstNameB
    some_stuff, ids, secondId, secondNameA, secondNameB

是否可以通过单个查询获取它，可能不使用子查询？

Answer 1

检查这个sqlfiddle

查询:

SELECT some_stuff, ids, id, nameA, nameB FROM TabA JOIN TabB ON INSTR(ids, id)

表结构:

CREATE TABLE IF NOT EXISTS `TabA` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `nameA` varchar(128) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `nameB` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `TabB` (
  `bid` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `ids` varchar(128) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `some_stuff` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`bid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO TabA(id,nameA,nameB) VALUES(1,"nm1","nmm1"),
(2,"nm2","nmm2"),
(3,"nm3","nmm3"),
(4,"nm4","nmm4"),
(5,"nm5","nmm5");

INSERT INTO TabB(bid,ids,some_stuff) VALUES(1,"1,2","some_stuff1"),
(2,"1,3","some_stuff2"),
(3,"2","some_stuff3"),
(4,"2,4","some_stuff4"),
(5,"1,2,3","some_stuff5");

输出:

SOME_STUFF  IDS ID  NAMEA   NAMEB

some_stuff1 1,2 1   nm1 nmm1
some_stuff1 1,2 2   nm2 nmm2
some_stuff2 1,3 1   nm1 nmm1
some_stuff2 1,3 3   nm3 nmm3
some_stuff3 2   2   nm2 nmm2
some_stuff4 2,4 2   nm2 nmm2
some_stuff4 2,4 4   nm4 nmm4
some_stuff5 1,2,3   1   nm1 nmm1
some_stuff5 1,2,3   2   nm2 nmm2
some_stuff5 1,2,3   3   nm3 nmm3

如果有任何错误，请告诉我。