mysql - SQL order by variable in inner statement

Question

我有一个像这样的 MySQL 查询:

SELECT * 
FROM backstage 
WHERE backstage_id IN (
    SELECT backstage_id 
    FROM visitor_counter 
    WHERE backstage_id !=0 
    GROUP BY backstage_id 
    ORDER BY COUNT( DISTINCT ( ip_address ) ) DESC 
) 
LIMIT 0 , 100

我得到了我想要的结果，但我想像内部问题一样按 COUNT( DISTINCT ( ip_address ) ) DESC 排序。

关于如何执行此操作的任何提示？

Answer 1

试一试，看看它是否能满足您的需求:

select bs.*
from backstage bs
inner join
(
select backstage_id,count(distinct ip_address) as distIpCount
from visitor_counter
where backstage_id !=0
group by backstage_id
) vc on vc.backstage_id = bs.backstage_id
order by vc.distIpCount desc
limit 0,100;