有两张表,
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
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
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)
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