WITH tt AS(
SELECT 100 ID,'语文' course,80 score FROM dual UNION ALL
SELECT 100,'数学',85 FROM dual UNION ALL
SELECT 100,'英语',90 FROM dual UNION ALL
SELECT 101,'语文',60 FROM dual UNION ALL
SELECT 101,'数学',70 FROM dual UNION ALL
SELECT 101,'英语',100 FROM dual UNION ALL
SELECT 102,'语文',100 FROM dual UNION ALL
SELECT 102,'数学',90 FROM dual UNION ALL
SELECT 102,'英语',90 FROM dual )
SELECT ID,COURSE, SCORE
FROM (SELECT ID,
COURSE,
SCORE,
RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM
FROM TT)
WHERE MM <= 2
SELECT 100 ID,'语文' course,80 score FROM dual UNION ALL
SELECT 100,'数学',85 FROM dual UNION ALL
SELECT 100,'英语',90 FROM dual UNION ALL
SELECT 101,'语文',60 FROM dual UNION ALL
SELECT 101,'数学',70 FROM dual UNION ALL
SELECT 101,'英语',100 FROM dual UNION ALL
SELECT 102,'语文',100 FROM dual UNION ALL
SELECT 102,'数学',90 FROM dual UNION ALL
SELECT 102,'英语',90 FROM dual )
SELECT ID,COURSE, SCORE
FROM (SELECT ID,
COURSE,
SCORE,
RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM
FROM TT)
WHERE MM <= 2
解决方案 »
- 简述如何决定是否在一张表上建立索引
- 别人建立的试图,我查询不到数据
- (紧急)请教一个在线生产系统的在用表的数据迁移和数据更新的问题(不能停应用),请高手不吝赐教,谢谢!
- 急啊在线等:比较oracle date类型数据
- 对同一个表根据不同的条件多次执行count(*),有什么办法加快其速度?
- 高分求Oracle售前方案
- Oracle中有哪些保留字?
- 请问各位大虾,如何在ORACLE73的初始化文件中写上两个归档目录,即log_archive_dest的路径有两个时怎么办?
- 字段number(38)取出值的接收(proc)?
- Linux平台Eclipse开发环境下配置Oracle ProC
- 调用oracle存储过程没结果出来,为什么
- weblogic数据源挂起、自动恢复
SELECT 100 ID,'语文' course,80 score FROM dual UNION ALL
SELECT 100,'数学',85 FROM dual UNION ALL
SELECT 100,'英语',90 FROM dual UNION ALL
SELECT 101,'语文',60 FROM dual UNION ALL
SELECT 101,'数学',70 FROM dual UNION ALL
SELECT 101,'英语',100 FROM dual UNION ALL
SELECT 102,'语文',100 FROM dual UNION ALL
SELECT 102,'数学',90 FROM dual UNION ALL
SELECT 102,'英语',90 FROM dual )
SELECT ID,COURSE, SCORE
FROM (SELECT ID,
COURSE,
SCORE,
RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM
FROM TT)
WHERE MM <= 2
是不是比较复杂点,繁琐点,有没有简单的?
能详细点吗?
SELECT ID,COURSE, SCORE FROM (SELECT ID, COURSE, SCORE, RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM FROM TT) WHERE MM <= 2
复杂?
能详细点吗?
SELECT ID,COURSE, SCORE FROM (SELECT ID, COURSE, SCORE, RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM FROM TT) WHERE MM <= 2
复杂?那个 RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM 它查出的值是什么啊?有点点小白
能详细点吗?
SELECT ID,COURSE, SCORE FROM (SELECT ID, COURSE, SCORE, RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM FROM TT) WHERE MM <= 2
复杂?那个 RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM 它查出的值是什么啊?有点点小白
你可以单独查查看看,
SELECT ID,
COURSE,
SCORE,
RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM
FROM TT
就是分组排序并分配序号,按每个分组分配序号。。尤其是当有重复值是,分配的序号相同。。
具体over用法你可以百度一下。。
能详细点吗?
SELECT ID,COURSE, SCORE FROM (SELECT ID, COURSE, SCORE, RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM FROM TT) WHERE MM <= 2
复杂?那个 RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM 它查出的值是什么啊?有点点小白
你可以单独查查看看,
SELECT ID,
COURSE,
SCORE,
RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM
FROM TT
就是分组排序并分配序号,按每个分组分配序号。。尤其是当有重复值是,分配的序号相同。。
具体over用法你可以百度一下。。非常感谢,