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*/
解决方案 »
- Mysql有NChar类型?
- mysqldump备份多个表,表的顺序是如何排列的?
- 一个表内的某个字段是vachar型,内容可能是gb2312,也可能是big5,怎么办才能避免乱码?
- sqlserver的datediff(day,startdate,enddate)+1函数如何在mysql 找到对应
- 初学MySQL数据库编程,碰到error LNK2019错误,请高手帮忙
- mysql无法启动???高分求助
- sql server 2000的数据库结构,如何导到mysql中去?有没有这样的工具?
- 如何查看mysql的log??
- [Err] 1242 - Subquery returns more than 1 row
- mysqld等占用太多内存,怎么办?
- 如何用mysqldump导出忽略主键的表数据?
- 一次可以来两个查询吗?
+------+-------+
| 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这个函数就可以了