在不使用"limit"的情况下获得相同的结果 (MySQL)

Question

如何在mysql中不使用"limit"获得相同的结果？

SELECT user_id
FROM user_interest
GROUP BY user_id
HAVING COUNT(user_id)
ORDER BY (COUNT(user_id)) DESC
LIMIT 2

Answer 1

如果您不想使用限制，这里有一些建议

select t2.user_id 
from (
select row_number() over (order by t1.ct desc) as rn, t1.userid 
from (
SELECT user_id, COUNT(user_id) as ct
FROM user_interest
GROUP BY user_id
HAVING COUNT(user_id)
)t1 
) as t2 where t2.rn < 3