Skynet

---------- ---------- 我的新 blog : liukaiyi.cublog.cn ---------- ----------

  BlogJava :: 首页 :: 联系 :: 聚合  :: 管理
  112 Posts :: 1 Stories :: 49 Comments :: 0 Trackbacks
问题描述:
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
posted on 2008-10-21 22:05 刘凯毅 阅读(1278) 评论(2)  编辑  收藏 所属分类: database

Feedback

# re: sql 语句笔记 2008-10-22 20:56 杨爱友
用inner join好像也可以  回复  更多评论
  

# re: sql 语句笔记 2008-10-23 14:23 刘凯毅
呵呵
当然 左连 , 右连 , 内连 语句都相同
  回复  更多评论
  


只有注册用户登录后才能发表评论。


网站导航: