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*/
(
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*/
+------+-------+
| 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)
from test1 a join test2 b on instr(b.name2,a.id1) >0
id1 name1
06 a6
02 a2
03 a3
09 a9
05 a5
select test1.*,instr(test2.name2,test1.id1) as num
from test1,test2
where instr(test2.name2,test1.id1) >0
order by num) temp
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 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这个函数就可以了