SELECT * FROM students WHERE EXISTS(
SELECT NULL FROM (SELECT 课程,MAX(成绩) over(PARTITION BY 课程) 成绩 FROM STUDENTS) A
WHERE A. 课程 = STUDENTS.课程
AND A.成绩 = STUDENTS.成绩)
SELECT NULL FROM (SELECT 课程,MAX(成绩) over(PARTITION BY 课程) 成绩 FROM STUDENTS) A
WHERE A. 课程 = STUDENTS.课程
AND A.成绩 = STUDENTS.成绩)
解决方案 »
- 新创建的存储过程 用execute immediate 报标识符过长
- 模糊的概念
- 如何去除重复(两个或更多字段相同)数据?
- 求助,SQL语句问题,谢谢!!!
- select top n 问题
- 寻问一个SQL语句的问题
- this question is very lower, I need you help! Thanks!!数据库启动问题
- 一个SQL语句的奇怪现象请大家解释
- sql/plus连接问题/
- 菜鸟问题:为什么字段DATE类型,不能保存数据?刚学。。。
- 求两个VARCHAR类型字段的时间差,菜鸟,急等。
- Ubuntu 12.04 64位的系统安装oracle10g(64位) ,安装数据库实例时出现问题。
SELECT KC,XM,BJ,CJ FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY KC ORDER BY CJ DESC) AS RN,KC,XM,BJ,CJ FROM TEST) WHERE RN = 1KC,XM,BJ,CJ
课程 姓名 班级 成绩
select count(*) from (select distinct 课程 from students)
select 课程,姓名,班级,max(成绩) over(partition by 课程) 成绩 from students
2L的是正解,总共有多少门课程的sql
create table students(
kc varchar2(20),
xm varchar2(20),
bj varchar2(20),
cj int
);
insert into students values('课程1','张三','三年级1班',69);
insert into students values('课程2','张三','三年级1班',90);
insert into students values('课程3','张三','三年级1班',87);
insert into students values('课程1','李四','三年级1班',88);
insert into students values('课程2','李四','三年级1班',78);
insert into students values('课程3','李四','三年级1班',76);
insert into students values('课程1','王五','三年级1班',65);
insert into students values('课程2','王五','三年级1班',86);
insert into students values('课程3','王五','三年级1班',90);
select count(distinct kc) as 总课程数 from students;select st_1.kc as "课程",st_1.xm as "姓名",st_1.bj as "年级",st_1.cj as "最高分" from students st_1
join( select kc,max(cj) max_cj from students group by kc) st
on(st_1.cj=st.max_cj and st_1.kc=st.kc)