问题描述：
dic_flow (id,status_id,status_name)
post_status(id,pre_status_id,post_status_id)
关系： dic_flow.status_id-(1.1)>post_status.pre_status_id,post_status_id
要结果： pre_status_id,pre_status_name,post_status_id,post_status_name
create table dic_flow(id int,status_id int , status_name varchar(20));
create table post_status(id int ,pre_status_id int ,post_status_id int);
insert into dic_flow values(1,1,'google');
insert into dic_flow values(2,3,'baidu');
insert into dic_flow values(3,8,'yahoo');
insert into post_status values(1,8,3);
select
tp.pre_status_id as pre_status_id,
( select td.status_name from dic_flow td where td.status_id=tp.pre_status_id) as pre_status_name ,
tp.post_status_id as post_status_id,
( select td.status_name from dic_flow td where td.status_id=tp.post_status_id) as post_status_name
from post_status tp;
+---------------+-----------------+----------------+------------------+
| pre_status_id | pre_status_name | post_status_id | post_status_name |
+---------------+-----------------+----------------+------------------+
| 8 | yahoo | 3 | baidu |
+---------------+-----------------+----------------+------------------+
1 row in set (0.00 sec)
Skynet(309290723) 21:11:57
子查询(性能优化版)
select
tp.pre_status_id as pre_status_id,
td1.status_name as pre_status_name ,
tp.post_status_id as post_status_id,
td2.status_name as post_status_name
from post_status tp , dic_flow td1 , dic_flow td2
where tp.pre_status_id=td1.status_id and tp.post_status_id=td2.status_id ;
Skynet(309290723) 21:12:08
+---------------+-----------------+----------------+------------------+
| pre_status_id | pre_status_name | post_status_id | post_status_name |
+---------------+-----------------+----------------+------------------+
| 8 | yahoo | 3 | baidu |
+---------------+-----------------+----------------+------------------+
Skynet(309290723) 21:16:35
再唠叨句
左连(性能再优化版)
select
tp.pre_status_id as pre_status_id,
td1.status_name as pre_status_name ,
tp.post_status_id as post_status_id,
td2.status_name as post_status_name
from post_status tp
left join dic_flow td1 on tp.pre_status_id=td1.status_id
left join dic_flow td2 on tp.post_status_id=td2.status_id ;
整理 www.blogjava.net/Good-Game