有一张表,里面有id,name,createtime 这里的id不是主键,,和另一张表对应. 现在的需求是.. 根据不同的id, 取每个id的根据日期排序的前十条... 请问大家有什么好的解决办法吗? 如果不同的id有二十个...我企不要执行二十次sql语句才能得到我想要的结果..............
问题补充:
1 s1 2008-09-09
1 s2 2008-09-02
1 s3 2008-09-01
2 t3 2008-09-01
2 t2 2008-09-03
2 t8 2008-09-09 假设这是数据库当中的数据.. 我想要的结果.. 1 s1 2008-09-09
1 s2 2008-09-02
2 t2 2008-09-03
2 t8 2008-09-09
结果的顺序没有关系..主要是能取到不同id的前二条.
问题补充:
1 s1 2008-09-09
1 s2 2008-09-02
1 s3 2008-09-01
2 t3 2008-09-01
2 t2 2008-09-03
2 t8 2008-09-09 假设这是数据库当中的数据.. 我想要的结果.. 1 s1 2008-09-09
1 s2 2008-09-02
2 t2 2008-09-03
2 t8 2008-09-09
结果的顺序没有关系..主要是能取到不同id的前二条.
http://topic.csdn.net/u/20081002/00/f8d90ba2-e2bb-412a-a0c5-1b6d518fc22a.html
select id,name,createtime from
(
select id,name,createtime, row_number() over(partition by id order by createtime) rn
from tableName
)
where rn < 10
select id,name,createtime
from (select rank() over(partition by id order by createtime desc) rn, S.*
from S) T
where T.rk <= 10;
select id,name,createtime
from (select dense_rank() over(partition by id order by createtime desc) rn, S.*
from S) T
where T.rk <= 10;
select id,name,createtime
from (select row_number() over(partition by id order by createtime desc) rn, S.*
from S) T
where T.rk <= 10;
(
select id,name,createtime, row_number() over(partition by id order by createtime) rn
from tableName
)
where rn < 3