创建测试表
create table test_tb
as
select user_id,user_name,type_id,user_time
from (
select 1 user_id,'aa' user_name,3 type_id,'2009-09-01' user_time from dual
union
select 2,'bb',3,'2009-09-02' from dual
union
select 3,'cc',3,'2009-09-04' from dual
union
select 4,'caec',5,'2009-09-02' from dual
union
select 5,'cdeefc',5,'2009-09-20' from dual
union
select 6,'acsfc',5,'2009-09-12' from dual
union
select 7,'csfc',5,'2009-09-22' from dual
union
select 8,'caaac',7,'2009-09-24' from dual
)a order by type_id,user_time desc
目的:获取每种type_id最新的两个用户
方法一,
select A.user_id,A.user_name,A.type_ID,A.user_time from
test_tb A
LEFT JOIN test_tb B
ON A.TYPE_ID=B.TYPE_ID AND A.user_id <=B.user_id
GROUP BY A.user_id,A.user_name,A.type_ID,A.user_time
HAVING COUNT(a.user_id) <=2
order by a.type_Id,a.user_id
方法二,
select A.user_id,A.user_name,A.type_ID,A.user_time from (
select rank() over (partition by type_Id order by user_id desc) Rank, a.* from test_tb a
)a where a.rank<=2 order by type_ID,user_id