34,592
社区成员
发帖
与我相关
我的任务
分享
create table cj(学号 varchar(10), 课程号 varchar(10), 成绩 int);
insert into cj(学号,课程号,成绩)
select
'TB04001','a001',80 union all select
'TB04002','a002',75 union all select
'TB04003','a001',77 union all select
'TB04004','a002',80 union all select
'TB04005','a001',80;
select 学号,成绩
from cj c1
where exists ( select 课程号, max(成绩) from cj c2
where c2.课程号=c1.课程号 and c2.课程号='a001' group by 课程号
having max(成绩)=c1.成绩 )
-------------------------------------------
TB04001 80
TB04005 80
create table cj(学号 varchar(10), 课程号 varchar(10), 成绩 int);
insert into cj(学号,课程号,成绩)
select
'TB04001','a001',80 union all select
'TB04002','a002',75 union all select
'TB04003','a001',77 union all select
'TB04004','a002',80 union all select
'TB04005','a001',80;
select 学号,成绩
from cj c1
where exists ( select 课程号, max(成绩) from cj c2
where c2.课程号=c1.课程号 and c2.课程号='a001' group by 课程号
having max(成绩)=c1.成绩 )
select 学号,成绩
from cj c1
where exists ( select 课程号, max(成绩) from cj c2
where c2.课程号=c1.课程号 and c2.课程号='a001' group by 课程号
having max(成绩)=c1.成绩 )