假设有这样一张表 create table test_db ( id number,
name varchar2(10),
count number,
logdate date)假设有这些个数据:1,a1,1,2010-02-03
2,a2,2,2010-02-03
.................
10,a10,10,2010-02-03
11,a11,11,2010-02-03
..............
30,a3,3,2010-02-04
31,a5,7,2010-02-04
............101,a2,10,2010-02-08
怎么取每天前10名的数据取出来?..........
name varchar2(10),
count number,
logdate date)假设有这些个数据:1,a1,1,2010-02-03
2,a2,2,2010-02-03
.................
10,a10,10,2010-02-03
11,a11,11,2010-02-03
..............
30,a3,3,2010-02-04
31,a5,7,2010-02-04
............101,a2,10,2010-02-08
怎么取每天前10名的数据取出来?..........
from (select a.*,
row_number() over(partition by a.logdate order by a.logdate, a.id) rn
from test_db a)
where rn <= 10
仅供参考!
取前10名。按照什么游戏方式取?你的logdate虽然是date型,但你的数据只有年月日。同样年月日的,需要考虑时分秒不?如果不考虑,又按照什么规格取前10名?ID?COUNT???
select id, name, count, logdate from (select test_db.*,row_number() over(partition by logdate order by logdate) rn from test_db) where rn <= 10;
from test_db t
where 10>(select count(*) from test_db where logdate=t.logdate and count>t.count);
-----迷惑在哪里?应该就是按照时间分组,然后按照count排序,取前十个吧。只有这个可能
select id, name, count, logdate
from (select id,
name,
count,
logdate,
row_number() over(partition by logdate order by count desc) rn
from test_db)
where rn <= 10
from (select id,
name,
count,
logdate,
row_number() over(partition by logdate order by count desc) rn
from test_db)
where rn <= 10
笑死我了。
1楼 的就可以解决。