一个复杂sql的求助

Question

 

Answer 1

如果你是hive等有row_number的sql 就这么写
select mobile,action,lendId
from (
select A.mobile,A.action,b.lendId,row_number() over(partition by A.mobile,A.action order by b.logtime) rw 
from A 
join B
on A.mobile=B.mobile 
where a.logtime<=b.logtime
) t 
where rw=1



如果你是mysql这种没有row_number的sql就这样写
select t.mobile,t.action,B.lendId
from (
select A.mobile,A.action,min(b.logtime) mn 
from A 
join B
on A.mobile=B.mobile 
where a.logtime<=b.logtime
) t 
join B
on t.mn=B.logtime