select mydate, max(score), min(score)
from mytable
group by mydate
from mytable
group by mydate
解决方案 »
- 在线等待高手相助: 启动时提示出错:ORA-00064: object is too large to allocate on this O/S(1,4800000)
- oracle的连接测试
- jdbc连Oracle数据库速度慢,请大侠给予指点
- 快请进帮忙,有个问题需要您的提示
- 我想创建一个用户,用该用户名登陆后只能看该用户名对应的模式
- 在线等,急。。。。。存储过程中的循环语法
- 请问SOLARIS9 X86 上能安装ORACLE9I 吗?用的是哪个版本呢?
- 有点难度的问题,在线等候
- !急急急急急急急!!!!!求救ORACLE工程师,无法起动数据库,ORA-00600错误!!!!!
- Oracle 查询时可以更改数据,同时最后一行有查询的合计
- 如何修改HOST名?
- 请教 ORACLE 8I 的安装问题
from mytable
group by mydate order by mydate desc where rownum<4;
select mydate, max(score), min(score)
from mytable where rownum<4 group by mydate order by mydate desc;
select * from tablename where myno<>3
楼主分明是要取得最后三天的每一天分值的两个最大值和两个最小值记录。我的方法:
SELECT mydate,
myscore
FROM
(
SELECT mydate,
myscore,
MAX(mydate) AS max_date,
RANK() OVER ( PARTITION BY mydate ORDER BY myscore ) AS rank_score
FROM mytable
)
WHERE TO_DATE(mydate) >= (TO_DATE(max_date) - 3)
AND rank_score <= 2
UNIONSELECT mydate,
myscore
FROM
(
SELECT mydate,
myscore,
MAX(mydate) AS max_date,
RANK() OVER ( PARTITION BY mydate ORDER BY myscore DESC ) AS rank_score
FROM mytable
)
WHERE TO_DATE(mydate) >= (TO_DATE(max_date) - 3)
AND rank_score <= 2 ORDER BY mydate,
myscroe简单解释一下,
1,以mydate为单位按照myscroe的升序取得排名,然后取得最后三天的每一天分值的两个最小值。
2,以mydate为单位按照myscroe的降序取得排名,然后取得最后三天的每一天分值的两个最大值。
3,合并结果集,并按照时间,分数排序。每天的纪录数不同也可以。
FROM
(SELECT mydate,
myscore,
RANK() OVER ( PARTITION BY mydate ORDER BY myscore ) AS rank_score
FROM mytable )
,
( SELECT MAX(mydate) AS max_date FROM mytable )
WHERE mydate >= TO_CHAR((TO_DATE(max_date) - 3),'YYYY-MM-DD')
AND rank_score <= 2
UNION
SELECT mydate, myscore
FROM
(SELECT mydate,
myscore, RANK() OVER ( PARTITION BY mydate ORDER BY myscore DESC ) AS rank_score
FROM mytable )
,
( SELECT MAX(mydate) AS max_date FROM mytable)
WHERE mydate >= TO_CHAR((TO_DATE(max_date) - 3),'YYYY-MM-DD')
AND rank_score <= 2
ORDER BY mydate,myscroe
mydate >= TO_CHAR((TO_DATE(max_date) - 3),'YYYY-MM-DD')
的=去掉。
这次测试过了,没有问题。SQL> select * from scores;
MYDATE MYNO MYSCORE
---------- ---------- ----------
2004-09-27 1 55
2004-09-27 2 75
2004-09-27 3 65
2004-09-27 4 85
2004-09-27 5 78
2004-09-28 1 55
2004-09-28 2 95
2004-09-28 3 15
2004-09-28 4 85
2004-09-28 5 78
2004-09-29 1 55
2004-09-29 2 75
2004-09-29 3 25
2004-09-29 4 85
2004-09-29 5 78
2004-09-30 1 55
2004-09-30 2 95
2004-09-30 3 65
2004-09-30 4 100
2004-09-30 5 7820行が選択されました。SQL>
ファイル afiedt.bufが書き込まれました
1 SELECT mydate, myscore
2 FROM
3 (SELECT mydate,
4 myscore,
5 RANK() OVER ( PARTITION BY mydate ORDER BY myscore ) AS rank_score
6 FROM scores )
7 ,
8 ( SELECT MAX(mydate) AS max_date FROM scores )
9 WHERE mydate > TO_CHAR((TO_DATE(max_date) - 3),'YYYY-MM-DD')
10 AND rank_score <= 2
11 UNION
12 SELECT mydate, myscore
13 FROM
14 (SELECT mydate,
15 myscore,
16 RANK() OVER ( PARTITION BY mydate ORDER BY myscore DESC ) AS rank_score
17 FROM scores )
18 ,
19 ( SELECT MAX(mydate) AS max_date FROM scores)
20 WHERE mydate > TO_CHAR((TO_DATE(max_date) - 3),'YYYY-MM-DD')
21 AND rank_score <= 2
22* ORDER BY mydate,myscore
SQL> /MYDATE MYSCORE
---------- ----------
2004-09-28 15
2004-09-28 55
2004-09-28 85
2004-09-28 95
2004-09-29 25
2004-09-29 55
2004-09-29 78
2004-09-29 85
2004-09-30 55
2004-09-30 65
2004-09-30 95
2004-09-30 10012行が選択されました。
SQL> SELECT mydate, myscore
2 FROM
3 (SELECT mydate,
4 myscore,
5 MAX(mydate) OVER () AS max_date,
6 RANK() OVER ( PARTITION BY mydate ORDER BY myscore )
AS rank_score_asc,
7 RANK() OVER ( PARTITION BY mydate ORDER BY myscore DESC )
AS rank_score_desc
8 FROM scores )
9 WHERE mydate > TO_CHAR((TO_DATE(max_date) - 3),'YYYY-MM-DD')
10 AND (rank_score_asc <= 2 or rank_score_desc <= 2)
11 ORDER BY mydate,myscore;MYDATE MYSCORE
---------- ----------
2004-09-28 15
2004-09-28 55
2004-09-28 85
2004-09-28 95
2004-09-29 25
2004-09-29 55
2004-09-29 78
2004-09-29 85
2004-09-30 55
2004-09-30 65
2004-09-30 95
2004-09-30 10012行が選択されました。