mysql> desc test4;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| idd | text | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)mysql> select * from test4;
+-------+
| idd |
+-------+
| 1,3,4 |
+-------+
1 row in set (0.00 sec)mysql> select * from test2;
+------+------+
| id | num |
+------+------+
| 1 | 2 |
| 1 | 4 |
| 1 | 8 |
| 1 | 20 |
| 6 | 83 |
| 6 | 33 |
| 5 | 22 |
| 3 | 50 |
| 3 | 51 |
| 4 | 51 |
| 4 | 52 |
| 4 | 55 |
| 6 | 96 |
| 7 | 91 |
| 7 | 102 |
| 7 | 111 |
| 6 | 1 |
| 6 | 1 |
| 6 | 1 |
| 6 | 2 |
| 6 | 12 |
| 6 | 13 |
| 7 | 15 |
| 7 | 5 |
| 7 | 6 |
| 7 | 10 |
| 1 | 2 |
| 99 | 20 |
+------+------+
28 rows in set (0.00 sec)mysql> select num from test4 ,test2 where id=test4.idd;
+------+
| num |
+------+
| 2 |
| 4 |
| 8 |
| 20 |
| 2 |
+------+
5 rows in set (0.00 sec)mysql> 问如何让id=1,3,4的所有记录都查询出来。即让id去遍历idd的每一个值
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| idd | text | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)mysql> select * from test4;
+-------+
| idd |
+-------+
| 1,3,4 |
+-------+
1 row in set (0.00 sec)mysql> select * from test2;
+------+------+
| id | num |
+------+------+
| 1 | 2 |
| 1 | 4 |
| 1 | 8 |
| 1 | 20 |
| 6 | 83 |
| 6 | 33 |
| 5 | 22 |
| 3 | 50 |
| 3 | 51 |
| 4 | 51 |
| 4 | 52 |
| 4 | 55 |
| 6 | 96 |
| 7 | 91 |
| 7 | 102 |
| 7 | 111 |
| 6 | 1 |
| 6 | 1 |
| 6 | 1 |
| 6 | 2 |
| 6 | 12 |
| 6 | 13 |
| 7 | 15 |
| 7 | 5 |
| 7 | 6 |
| 7 | 10 |
| 1 | 2 |
| 99 | 20 |
+------+------+
28 rows in set (0.00 sec)mysql> select num from test4 ,test2 where id=test4.idd;
+------+
| num |
+------+
| 2 |
| 4 |
| 8 |
| 20 |
| 2 |
+------+
5 rows in set (0.00 sec)mysql> 问如何让id=1,3,4的所有记录都查询出来。即让id去遍历idd的每一个值
+------+------+
| id | num |
+------+------+
| 1 | 2 |
| 1 | 4 |
| 1 | 8 |
| 1 | 20 |
| 3 | 50 |
| 3 | 51 |
| 4 | 51 |
| 4 | 52 |
| 4 | 55 |
| 1 | 2 |
+------+------+
10 rows in set (0.00 sec)
select * from test2 where find_in_set(id,(select group_concat(idd) from test4));
from test4 a , test2 b
where find_in_set(b.id, a.idd)
如果想使用索引,则必须使用 where id in (1,2,3,4) 这种方式,这样需要你在程序中,或者存储过程中来动态构造这个SQL语句然后提交执行。