mysql5.7以及以后版本将会对order 语句有默认的规则,因此 在子查询内部 使用order by 可能会被忽略。网上解决的方法啊大多数是在子查询 的order by xxx desc 后增加 limit 99999.
本地测试如下:
select tab_.*,ias.name as studio_name from (SELECT adp.id ,adp.creator as creator,adp.online_time,adp.name as name,adpp.day_profit,adpp.week_profit,adpp.month_profit,adpp.total_asset as total_profit,adp.subscription_num,adp.updatetime,ad.real_name as adname,'免费' as type,adp.is_online from table adp LEFT JOIN tableadpp adpp ON adp.id =adpp.portfolio_id LEFT JOIN table ad on adp.creator =ad.id WHERE adp.approve_status=1 ORDER BY total_profit desc, adp.id DESC limit 99999)tab_ left join table ia on tab_.creator=ia.id left join iadvisor_studio ias on ia.studio_id=ias.id where 1=1 and ias.status=1 limit 0,20
然而并没有出现任何好转,排序依然无效。
最后解决方法是 讲 order by 从子查询中提取到 外面
select tab_.*,ias.name as studio_name from (SELECT adp.id ,adp.creator as creator,adp.online_time,adp.name as name,adpp.day_profit,adpp.week_profit,adpp.month_profit,adpp.total_asset as total_profit,adp.subscription_num,adp.updatetime,ad.real_name as table,'免费' as type,adp.is_online from table adp LEFT JOIN table adpp ON adp.id =adpp.portfolio_id LEFT JOIN table ad on adp.creator =ad.id WHERE adp.approve_status=1 )tab_ left join table ia on tab_.creator=ia.id left join iadvisor_studio ias on ia.studio_id=ias.id where 1=1 and ias.status=1 ORDER BY total_profit desc, adp.id DESC
如上 解决!