表:
ID title details date
1 "aaa" asdfa
1 "bbb" asdaf
1 "ccc" asdsa
2 "fff" adfasdf
2 "retrt" asfasdfa
3 "afdsaf" afsdsafsa
3 "afdsaf" afdsaf
3 "afdsa" asfdsa要求一sql语句:查询ID相同的前两条数据。并以date排序 查询后的结果ID title details date
1 "aaa" asdfa
1 "bbb" asdaf
2 "fff" adfasdf
2 "retrt" asfasdfa
3 "afdsaf" afsdsafsa
3 "afdsaf" afdsaf
ID title details date
1 "aaa" asdfa
1 "bbb" asdaf
1 "ccc" asdsa
2 "fff" adfasdf
2 "retrt" asfasdfa
3 "afdsaf" afsdsafsa
3 "afdsaf" afdsaf
3 "afdsa" asfdsa要求一sql语句:查询ID相同的前两条数据。并以date排序 查询后的结果ID title details date
1 "aaa" asdfa
1 "bbb" asdaf
2 "fff" adfasdf
2 "retrt" asfasdfa
3 "afdsaf" afsdsafsa
3 "afdsaf" afdsaf
select test.*,
row_number() over(partition by id order by date)rn
from test)
where rn in(1,2)
order by id,date;
from test
where row_num <=2;
select test.*,
row_number() over(partition by id order by date)rn
from test)
where rn <= 2
order by id,date;
from
(
select test.*, row_number() over(partition by id order by "date" desc) rn
from test
)
where rn <=2;
--楼主是要按date 是升序还是降序(asc/desc)
select ID,title,details,date
from
(select ID,title,details,date,row_number over(partition by ID order by date desc) rn
from tb) a
where rn<=2