select 学号,max(成绩)
from cj
where 课程号='a001'
group by 学号,成绩
having 成绩=max(成绩)很简单的一个SQL语法题,但是就是解决不了。想求一门课程(a001)获得最高分的同学学号和成绩,怎么按以上的语法,是获得所有学了这门课程的同学学号和成绩?
语法并没有错啊?请大家帮忙看看,这个SQL语句出了什么问题?
from cj
where 课程号='a001'
group by 学号,成绩
having 成绩=max(成绩)很简单的一个SQL语法题,但是就是解决不了。想求一门课程(a001)获得最高分的同学学号和成绩,怎么按以上的语法,是获得所有学了这门课程的同学学号和成绩?
语法并没有错啊?请大家帮忙看看,这个SQL语句出了什么问题?
from cj
where 课程号='a001'
and 成绩= (select max(成绩) from cj)
直接写max(成绩)不符合语法
from cj
where 课程号='a001'这样就可以了
from cj c1
where exists ( select 课程号, max(成绩) from cj c2
where c2.课程号=c1.课程号 and c2.课程号='a001' group by 课程号
having max(成绩)=c1.成绩 )
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.成绩 )
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
from cj
where 课程号='a001' 同意5楼。
-------------------------------------------
select 学号,max(成绩)
from cj
where 课程号='a001'
---------------------------
消息 8120,级别 16,状态 1,第 1 行
选择列表中的列 'cj.学号' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
from cj c1
where exists ( select 课程号, max(成绩) from cj c2
where c2.课程号=c1.课程号 and c2.课程号='a001' group by 课程号
having max(成绩)=c1.成绩 )
from cj
where 课程号='a001'
and 成绩= (select max(成绩) from cj where 课程号='a001')
from sj a group by 課程號
from cj
where 课程号='a001'
and 成绩= (select max(成绩) from cj where 课程号='a001')