mysql里，怎么查询某列是否包含另一个查询的结果集？？

[qq4285855]

drop table if exists test_A;
create table test_A (id int PRIMARY key,name varchar(50),type int);

insert into test_A values(1,'aaa',1);
insert into test_A values(2,'bbb',1);
insert into test_A values(3,'ccc',2);

drop table if exists test_B;
create table test_B (id int,names varchar(50));

insert into test_B values(1,'aaa,bbb,ccc');
insert into test_B values(2,'aaa,bbb');
insert into test_B values(3,'bbb,ccc');
insert into test_B values(4,'bbb,aaa,ccc');

test_B 表里的 names 就是test_A 表的 name，用逗号拼接在一起。我是想查询 test_A 表 type=1 的 name 包含在test_B 表names 列的结果集。
有什么好办法？？

[持久的烧烤]

是夜深人静了吗。。。。

[持久的烧烤]

没人知道吗。。。。

[ACMAIN_CHM]

引用

我是想查询 test_A 表 type=1 的 name 包含在test_B 表names 列的结果集。

mysql> select * from test_A; +----+------+------+ | id | name | type | +----+------+------+ | 1 | aaa | 1 | | 2 | bbb | 1 | | 3 | ccc | 2 | +----+------+------+ 3 rows in set (0.00 sec) mysql> select * from test_b; +------+-------------+ | id | names | +------+-------------+ | 1 | aaa,bbb,ccc | | 2 | aaa,bbb | | 3 | bbb,ccc | | 4 | bbb,aaa,ccc | +------+-------------+ 4 rows in set (0.00 sec) mysql> select * from test_A where type=1 -> and exists ( select 1 from test_B where find_in_set(test_A.name,names)); +----+------+------+ | id | name | type | +----+------+------+ | 1 | aaa | 1 | | 2 | bbb | 1 | +----+------+------+ 2 rows in set (0.00 sec) mysql>

[rick-he]

引用 3 楼 ACMAIN_CHM 的回复:

引用

我是想查询 test_A 表 type=1 的 name 包含在test_B 表names 列的结果集。

mysql> select * from test_A; +----+------+------+ | id | name | type | +----+------+------+ | 1 | aaa | 1 | | 2 | bbb | 1 | | 3 | ccc | 2 | +----+------+------+ 3 rows in set (0.00 sec) mysql> select * from test_b; +------+-------------+ | id | names | +------+-------------+ | 1 | aaa,bbb,ccc | | 2 | aaa,bbb | | 3 | bbb,ccc | | 4 | bbb,aaa,ccc | +------+-------------+ 4 rows in set (0.00 sec) mysql> select * from test_A where type=1 -> and exists ( select 1 from test_B where find_in_set(test_A.name,names)); +----+------+------+ | id | name | type | +----+------+------+ | 1 | aaa | 1 | | 2 | bbb | 1 | +----+------+------+ 2 rows in set (0.00 sec) mysql>

用find_in_set,话说这么晚还没睡?