两张表联合起来求一SQL 急···

[jody_go]

有两张表,
table01:
id htno vaule
01 2011 10
02 2011 12
03 2011 11
011 2012 4
012 2012 6
013 2012 7
016 2012 9
020 3011 7
022 3011 8
...

table02:
htno wt
2011 200
2012 210
3011 220

现在想通过两张表关联，求得每个htno的最小id时的value和最大id时的value信息，欲求得的结果：
htno wt min_id min_id_value max_id max_id_value
2011 200 01 10 03 11
2012 210 011 4 016 9
3011 220 020 7 022 8

[gelyon]

两种情况都有回答http://topic.csdn.net/u/20110419/16/e162db8b-5a11-4b7b-ad0c-626cf442fb6b.html?78929

[ssqtjffcu]

不是已经帮你答了么！！！

[304的的哥]

with t1 as(
     select '01' id,'2011' htno,10 val from dual union all
     select '02','2011',12 from dual union all
     select '03','2011',11 from dual union all
     select '011','2012',4 from dual union all
     select '012','2012',6 from dual union all
     select '013','2012',7 from dual union all
     select '016','2012',9 from dual union all
     select '020','3011',7 from dual union all
     select '022','3011',8 from dual),
     t2 as(
     select '2011' htno,200 wt from dual union all
     select '2012',210 from dual union all
     select '3011',220 from dual)
select t1.htno htno,
       t2.wt wt,
       min(id) min_id,
       min(t1.val) min_id_val,
       max(id) max_id,
       max(t1.val) max_id_val
from t1,t2 
where t1.htno=t2.htno
group by t1.htno,t2.wt;
--
HTNO         WT MIN_ID MIN_ID_VAL MAX_ID MAX_ID_VAL
---- ---------- ------ ---------- ------ ----------
2011        200 01             10 03             12
2012        210 011             4 016             9
3011        220 020             7 022             8

[dawugui]

select t.* , t1.* , t2.*
from table02 t , table01 t1 , table01 t2
where t.htno = t1.htno and t.htno = t2.htno and
t1.id = (select min(id) from table01 where htno = t1.htno) and
t2.id = (select max(id) from table01 where htno = t2.htno) 

select t.* , t1.* , t2.*
from table02 t , table01 t1 , table01 t2
where t.htno = t1.htno and t.htno = t2.htno and
not exists (select 1 from table01 where htno = t1.htno and id < t1.id) and
not exists (select 1 from table01 where htno = t1.htno and id > t1.id)