Question

以下plsql语句如何改为取最新一条数据

原语句：select (select t1.mc
from table1 t1
where t1.key = t.key
and t1.rownum = 1)
from table t
如上，只能取随机一条数据，并不能取最新一条
若改为
select (select mc
from (select t1.key,
t1.mc,
t1.shijian,
row_number() over(partition by t1.key order by t1.shijian desc) rn
from table1 t1)
where key = t.key
and rn = 1)
from table t
这个倒是可以，可惜table1表数据过大，非常影响速度。
所以，请教下各位高手，有什么方法能实现需求吗？

Answer 1

select mc
from (select t1.key, t1.mc, t1.shijian
from table1 t1
order by t1.shijian desc
)
where rownum = 1

追问

select (select mc
from (select t1.mc
from table1 t1
where t1.key = t.key
order by t1.shijian desc)
where rownum = 1)
from table t
这样？

追答

select mc
from (select t1.key, t1.mc, t1.shijian
from table1 t1
order by t1.shijian desc
)
where rownum = 1 就是这样啊，有什么问题吗？

追问

有，我需要关联table表，而且因为之后要关联好多张表，不能让table表与table1表同级。
另，该语句只是查询table1表的最后一条记录，如何关联上table表呢？

追答

select * (select mc
from (select t1.mc
from table1 t1
where t1.key = t.key
order by t1.shijian desc)
where rownum = 1) left outer join ...后面可以串表啊