select D.id,B.class,B.teacher,D.name,D.age,D.year
from B,
(
select A.id,A.name,A.age,A.year
from A
where A.year > (select min(C.year) from a C where A.id = C.id)
) D
where D.id = B.id
from B,
(
select A.id,A.name,A.age,A.year
from A
where A.year > (select min(C.year) from a C where A.id = C.id)
) D
where D.id = B.id
SQL> select * from t1;ID NAME AGE YEAR
----- -------------------- ---------- --------------------
001 张三 18 2001.1
002 李四 20 2001.2
003 王五 21 2001.3
001 张四三 18 2001.5
002 李四三 20 2001.7SQL> select * from t2;ID CLASS TEACHER
----- -------------------- --------------------
001 class1 a
002 class3 b
003 class1 cSQL> select t2.*,t.name,t.age,t.year from t2,(
2 select id,name,age,year from
3 (select t1.*,dense_rank() over(partition by id order by year desc)y from t1 ) where y=1)t
4 where t.id=t2.id;ID CLASS TEACHER NAME AGE YEAR
----- -------------------- -------------------- -------------------- ---------- --------------------
001 class1 a 张四三 18 2001.5
002 class3 b 李四三 20 2001.7
003 class1 c 王五 21 2001.3
from
( select a.id, a.name, a.age, a.year
from t1 a, t1 b
where (a.id = b.id) and (a.year > b.year) and
(a.year = (select max(year) from t1 c where c.id = a.id ))
) x, t2
where x.id = t2.id
(假设year 每次都不同)。
应该表 a只保存最终结果.
加上一各a_tran的表.
记录每次变更.
表 b
id class teacher
001 class1 a
002 class3 b
003 class1 c
.......... 省略表 a
id name age year
001 张三 18 2001.1
001 张四三 18 2001.5
002 李四 20 2001.2
002 李四三 20 2001.7
要求通过sql语句得到如下表
id class teacher name age year
001 class1 a 张四三 18 2001.5
002 class3 b 李四三 20 2001.7
from
( select a.id, a.name, a.age, a.year
from t1 a, t1 b
where (a.id = b.id) and (a.year > b.year) and
(a.year = (select max(year) from t1 c where c.id = a.id ))
) x, t2
where x.id = t2.id
(假设year 每次都不同)。 里面有表c吗, 是别名
id class teacher
001 class1 a
002 class3 b
003 class1 c
.......... 省略表 a
id name age year
001 张三 18 2001.1
001 张四三 18 2001.5
002 李四 20 2001.2
002 李四三 20 2001.7
要求通过sql语句得到如下表
id class teacher name age year
001 class1 a 张四三 18 2001.5
002 class3 b 李四三 20 2001.7
003 class1 c 王五 21 2001.3