SQL优化：子查询->派生表->join关联

今天看到论坛有人提问，需要对语句进行优化：

select *,
      (select count(1) 
	   from `ol_classify` 
	   where recommend_id = u.user_id AND 
	         `Creation_time` >= 1477929600 ) count 
from `ol_classify` u 
where u.state >0 
HAVING count >= 4 select *,
      (se