现有两个表如下:
表A:AID为主键
AID AName
______________
1 a
2 b
3 c
...
表B:ID为主键
ID Name AID
-------------------------
1 aa 1
2 bb 2
3 ab 1
4 cc 3
5 a3 1
6 b2 2
...
现要写出查询出表A内容对应在表B中ID最大的记录的SQL语句
查询结果如下:
ID Name AID AName
-------------------------------
5 a3 1 a
6 b2 2 b
4 cc 3 c
表A:AID为主键
AID AName
______________
1 a
2 b
3 c
...
表B:ID为主键
ID Name AID
-------------------------
1 aa 1
2 bb 2
3 ab 1
4 cc 3
5 a3 1
6 b2 2
...
现要写出查询出表A内容对应在表B中ID最大的记录的SQL语句
查询结果如下:
ID Name AID AName
-------------------------------
5 a3 1 a
6 b2 2 b
4 cc 3 c
select b.id, b.name, a.aid, a.aname, rownum() over(partition by a.aid, a.aname order by b.id desc) id from (
select b.id, b.name, a.aid, a.aname from a, b where a.aid=b.bid)
) where id=1
from aaa a,bbb b
where a.aid=b.aid and b.id in (
select max(b.id)
from bbb b
group by aid)
order by aname