大家好,我现在遇到这样一个问题,有一个表里面有这些数据user_name domain_name
A www.163.com
A www.263.com
A www.sina.com
A www.sohu.com
A www.chinaren.com
B www.163.com
B www.263.com
B www.sina.com
B www.sohu.com我现在用的语句是:
select user_name,domain_name from table group by user_name,domain_name我现在的问题是想查询显示,每个用户的前3条记录,user_name就是用户名,显示A用户的3条记录,B用户的3条记录。但是我现在的sql语句是显示每个用户的所有数据,我如何过滤才能显示每个用户的前N条数据呢?谢谢大家了。
A www.163.com
A www.263.com
A www.sina.com
A www.sohu.com
A www.chinaren.com
B www.163.com
B www.263.com
B www.sina.com
B www.sohu.com我现在用的语句是:
select user_name,domain_name from table group by user_name,domain_name我现在的问题是想查询显示,每个用户的前3条记录,user_name就是用户名,显示A用户的3条记录,B用户的3条记录。但是我现在的sql语句是显示每个用户的所有数据,我如何过滤才能显示每个用户的前N条数据呢?谢谢大家了。
from (select user_name,
domain_name,
row_number() over(partition by username order by domain_name) rn
from t)
where rn <= 3;
SELECT USER_NAME,DOMAIN_NAME FROM (
SELECT USER_NAME,DOMAIN_NAME,ROW_NUMBER()OVER(PARTITION BY USERNAME ORDER BY USERBANE) RN
FROM TABLE
) WHERE RN<=3;
from (select user_name,
domain_name,
row_number() over(partition by user_name order by domain_name desc) rn)
where rn <= 3
from (select user_name a, domain_name b, rownum
from tableA
order by b desc) t
where rownum <= 3;
from
(
select user_name,domain_name
from table
group by user_name,domain_name
order by user_name,domain_name
) cur
where rownum <=3这是标准SQL,不管是什么数据库都应该可以的,不过效率不太高!