17,086
社区成员
发帖
与我相关
我的任务
分享
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)
SELECT COUNT(DISTINCT 课程) FROM STUDENTS
SELECT * FROM students WHERE EXISTS(
SELECT NULL FROM (SELECT 课程,MAX(成绩) over(PARTITION BY 课程) 成绩 FROM STUDENTS) A
WHERE A. 课程 = STUDENTS.课程
AND A.成绩 = STUDENTS.成绩)
select b.* from students b,(select 课程,max(成绩) 成绩 from students group by 课程) a where a.课程=b.课程 and a.成绩=b.成绩
select count(*) from (select distinct 课程 from students)
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 = 1