```select recommend_user_id,count(recommend_user_id) count,MAX(time) time
from(
select i.user_id,u.recommend_user_id,i.time
from users u
right join (select b.user_id, b.time
from (select u.id, u.time
from users u
where time >= to_date('2017/12/01', 'yyyy/mm/dd')
and time < to_date('2018/01/19', 'yyyy/mm/dd')
and u.recommend_user_id <> 0) a
inner join (select user_id, MAX(time) time
from invests
where time >= to_date('2017/12/01', 'yyyy/mm/dd')
and time < to_date('2018/01/19', 'yyyy/mm/dd')
group by user_id
order by time) b
on a.id = b.user_id) i
on u.id = i.user_id
) group by recommend_user_id order by count desc,time
select i.user_id,u.recommend_user_id,i.time
from users u
right join (select b.user_id, b.time
from (select u.id, u.time
from users u
where time >= to_date('2017/12/01', 'yyyy/mm/dd')
and time < to_date('2018/01/19', 'yyyy/mm/dd')
and u.recommend_user_id <> 0) a
inner join (select user_id, MAX(time) time
from invests
where time >= to_date('2017/12/01', 'yyyy/mm/dd')
and time < to_date('2018/01/19', 'yyyy/mm/dd')
group by user_id
order by time) b
on a.id = b.user_id) i
on u.id = i.user_id
这一段取到时间的,到最后一层时间就为空了,为什么?
从内层逐个运行下，也许就有答案了
语句分步执行，一段代码一段执行，大部分原因关联条件导致的
别名别用关键字， order by count desc, time 换成order by count(recommend_user_id) 试试