select a.id as orderId, b.userName, c.status, c.updateTime from Orders as a,Users as b,OrdersLog as c where a.id=c.orderId and b.id=c.userId and not exists(select 1 from OrdersLog where OrdersLog=c.OrdersLog and updateTime>c.updateTime)
select orderId,userName, status, updateTime from (select *,rn=row_number() over (partition by orderid order by updatetime desc) from orderslog) a,users where a.userid=b.userid and rn='1'
select c.id as orderId,userName, status, updateTime from (select *,rn=row_number() over (partition by orderid order by updatetime desc) from orderslog) a,users,Orders c where a.userid=b.userid and a.orderid=c.orderid and rn='1' orderId userName status updateTime
大神有没有想过可以用max(updateTime)来解决的
try this, It 's better have an index on OrdersLog(orderId).select a.orderId,b.userName,a.status,a.updateTime from OrdersLog a inner join Users b on a.userId=b.id inner join (select orderId,max(id) 'maxid' from OrdersLog group by orderId) c on a.orderId=c.orderId and a.id=c.maxid
If he don't have index,what's the better?
大神有没有想过可以用max(updateTime)来解决的 总结各位大神最终的成品select orderId,userName,status,updateTime from OrdersLog left join Users on OrdersLog.userId=Users.id where OrdersLog.id in (select max(id) as logId from OrdersLog group by orderId)
I think it only can full table scan the OrdersLog..
a.id as orderId,
b.userName,
c.status,
c.updateTime
from
Orders as a,Users as b,OrdersLog as c
where
a.id=c.orderId and b.id=c.userId
and
not exists(select 1 from OrdersLog where OrdersLog=c.OrdersLog and updateTime>c.updateTime)
where a.userid=b.userid and rn='1'
where a.userid=b.userid and a.orderid=c.orderid and rn='1'
orderId userName status updateTime
from OrdersLog a
inner join Users b on a.userId=b.id
inner join
(select orderId,max(id) 'maxid'
from OrdersLog
group by orderId) c on a.orderId=c.orderId and a.id=c.maxid
总结各位大神最终的成品select orderId,userName,status,updateTime from OrdersLog left join Users on OrdersLog.userId=Users.id where OrdersLog.id in (select max(id) as logId from OrdersLog group by orderId)