我有一个相当困难的问题。
我需要在一个名为'key_phrases'(columns:'user','phrase','tfw')的表上编写一个查询,该表检索每个用户前10%的行,这些行的最大值为'tfw'列。查询应返回所有列。
提前谢谢你,尼尔
最佳答案
select user, phrase, tfw
from key_phrases t1
join (
select count(*) total_rows_per_user, user
from key_phrases
group by user
) t2 on t1.user = t2.user
where (
select count(*) from key_phrases t3
where t3.user = t1.user
and t3.tfw >= t1.tfw
) / total_rows_per_user <= .1
另一个使用变量的查询应该更快
select user, phrase, tfw,
if(@prev_user = user, @user_count := @user_count + 1, @user_count := 1),
@prev_user := user
from key_phrases t1
join (
select count(*) total_rows_per_user, user
from key_phrases
group by user
) t2 on t1.user = t2.user
cross join (select @user_count := 1, @prev_user := null) t3
where @user_count / total_rows_per_user >= .9
order by user, tfw
关于mysql - SQL选择前百分之n行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/25549172/