select * from table_a a,
(select * from table_b b where b.id in(select max(id) from table_b c group by orderNum)) d
where a.orderNum=d.orderNum
...全文
1105打赏收藏
几张有重复记录的表关联查询取最新的记录,如何优化?
table_a ---------------------- id orderNum value 1 a001 v001 2 a001 v002 table_b ---------------------- id orderNum status 1 a001 a 2 a001 b 3 a001 c 4 b001 d 5 b001 e 这是我想到的办法,虽然可以运行,可是在table_b表的数据很多的时候,效率很慢,目前测试20多万的数据,感觉速度已经不可接受。请高手支招!!!谢谢了。 selec
select a.id,a.ordernum,a.value,b.status
from a,(
select ordernum ,status,row_number()over(partition by ordernum order by id desc)row_num
from c)b
where a.ordernum = b.ordernum and b.row_num=1;