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 列的结果集。
有什么好办法??
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 列的结果集。
有什么好办法??
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>
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,话说这么晚还没睡?