Question

Oracle 多表联合查询 取最近一条

想查询出每个用户最后一次消费的时间和金额
ORACLE 数据库

Answer 1

select a.id,a.u_name,b.td,b.yuan from 表a a,表b b
where a.id=b.id
and (b.id,b.dt) in (select c.id,max(c.dt) from 表b c group by c.id )
大概是这样，还有其他写法，思路差不多

Answer 2

select a.id,a.u_name,xx.dt,xx.yuan
from a left outer join
(
select b.a_id,b.dt,b.yuan from b,
(select a_id,max(dt) dt
from b
) bb where b.a_id = bb.a_id and b.dt = bb.dt
) xx
on a.id = xx.a_id

Answer 3

--对a,b两表做一连接查询
select a.id,a.U_Name,b.DT,b.Yuan from
(select * from A)a,
(select * from B)b
where a.id =b.id(+)

Answer 4

select a,id,a,u_name,max(b.dt),b,yuan
from a,b
where a.id = b.id(+)
group by a,id,a,u_name,b.yuan;

Answer 5

select A.ID,A.U_Name,C.DT,C.Yuan
from a,
(
select ID,DT,Yuan from B
where (ID,DT) in
(
select ID,max(DT) from B group by id
)
) c
where A.ID=C.ID