有两张表,
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和最大id的信息,欲求得的结果:
htno id value wt
2011 01 10 200
2011 03 11 200
2012 011 4 210
2012 013 7 210
3011 016 7 220
3011 022 8 220
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和最大id的信息,欲求得的结果:
htno id value wt
2011 01 10 200
2011 03 11 200
2012 011 4 210
2012 013 7 210
3011 016 7 220
3011 022 8 220
htno min_id max_id value wt
2011 01 03 10 200
2012 011 013 4 210
3011 016 022 7 220
create table t1(id varchar(20),htno varchar2(20),vaule number)
create table t2(htno varchar2(20),wt number);
insert into t1 values ('01','2011',10);
insert into t1 values ('02','2011',12);
insert into t1 values ('03','2011',11);
insert into t1 values ('011','2012',4);
insert into t1 values ('012','2012',6);
insert into t1 values ('013','2012',7);
insert into t1 values ('016','2012',9);
insert into t1 values ('020','3011',7);
insert into t1 values ('022','3011',8);insert into t2 values ('2011',200);
insert into t2 values ('2012',210);
insert into t2 values ('2012',220);select t1.htno,min(id),max(id),t2.wt from t1,t2 where t1.htno=t2.htno group by t1.htno,t2.wt 2011 01 03 200
2012 011 016 210
3011 020 022 220
--1
select a.htno,a.id,a.value,b.wt
from table01 a,table02 b
where a.htno=b.htno and a.id in(
select max(id) id from table01 where a.htno=htno
union all
select min(id) id from table01 where a.htno=htno
)
--2
select a.htno,c.min_id,c.max_id,a.value,b.wt
from table01 a,
table02 b,
(select htno,
max(id) max_id,
min(id) min_id
from table01 group by htno) c
where a.htno=b.htno and a.htno=c.htno and (a.id=c.min_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,
min(id),
max(id),
min(t1.val),
t2.wt
from t1,t2
where t1.htno=t2.htno
group by t1.htno,t2.wt;
--
HTNO MIN(ID) MAX(ID) MIN(T1.VAL) WT
---- ------- ------- ----------- ----------
2011 01 03 10 200
2012 011 016 4 210
3011 020 022 7 220
from table01 a,table02 b
where a.htno=b.htno and a.id in(
select max(id) id from table01 where a.htno=htno
union all
select min(id) id from table01 where a.htno=htno
)