这个sql语句怎么写？？？

[zyk220]

create table test1
(
id1 varchar(10),
name1 varchar(20)
)
insert into test1 select '01','a1'
insert into test1 select '02','a2'
insert into test1 select '03','a3'
insert into test1 select '04','a4'
insert into test1 select '05','a5'
insert into test1 select '06','a6'
insert into test1 select '07','a7'
insert into test1 select '08','a8'
insert into test1 select '09','a9'
insert into test1 select '10','a10'

create table test2
(
id2 varchar(10),
name2 varchar(20)
)
insert into test2 select '11','06,02,03,09,05'

/*
根据test2表要得到这样的报表：
id1 name1
06 a6
02 a2
03 a3
09 a9
05 a5

*/

[小小小小周]

mysql> select *from test1;
+------+-------+
| id1  | name1 |
+------+-------+
| 01   | a1    |
| 02   | a2    |
| 03   | a3    |
| 04   | a4    |
| 05   | a5    |
| 06   | a6    |
| 07   | a7    |
| 08   | a8    |
| 09   | a9    |
| 10   | a10   |
+------+-------+
10 rows in set (0.00 sec)

mysql> select *From test2;
+------+----------------+
| id2  | name2          |
+------+----------------+
| 11   | 06,02,03,09,05 |
+------+----------------+
1 row in set (0.00 sec)

mysql> select test1.* from test1,test2
    -> where instr(test2.name2,test1.id1) >0;
+------+-------+
| id1  | name1 |
+------+-------+
| 02   | a2    |
| 03   | a3    |
| 05   | a5    |
| 06   | a6    |
| 09   | a9    |
+------+-------+
5 rows in set (0.00 sec)

[feixianxxx]

select a.id1,a.name1
from test1 a join test2 b on instr(b.name2,a.id1) >0

[feixianxxx]

这里的instr 还可以用 locate like等代替~

[zyk220]

楼上的兄弟 能否按照这个顺序输出
id1 name1
06 a6
02 a2
03 a3
09 a9
05 a5

[小小小小周]

select id1,name1 from (
select test1.*,instr(test2.name2,test1.id1) as num   
from test1,test2 
where instr(test2.name2,test1.id1) >0
order by num) temp 

[zyk220]

5楼的兄弟 非常感谢，但是还是有一个问题如果是这样的数据
create table test1
(
id1 varchar(10),
name1 varchar(20)
)
insert into test1 select '01','a1'
insert into test1 select '02','a2'
insert into test1 select '03','a3'
insert into test1 select '04','a4'
insert into test1 select '05','a5'
insert into test1 select '06','a6'
insert into test1 select '07','a7'
insert into test1 select '08','a8'
insert into test1 select '09','a9'
insert into test1 select '10','a10'
insert into test1 select '221','a221'
insert into test1 select '1221','a1221'



create table test2
(
id2 varchar(10),
name2 varchar(20)
)
insert into test2 select '11','06,1221,03,09,05'

/*
得到的是这样的报表：
id1 name1
06 a6
1221 a1221
221 a221
03 a3
09 a9
05 a5

多出一行 221 a221

[小小小小周]

select id1,name1 from (
select test1.*,find_in_set(test1.id1,test2.name2) as num   
from test1,test2 
where find_in_set(test1.id1,test2.name2)>0
order by num) temp

这个函数就可以了