这个意思?select s_id,s_name,sum(s_score) s_score from t group by s_id,s_name order by s_id
你是要查各科总分第一的学生吧,如下: select * from (select s.*, dense_rank() over(partition by s.s_cname order by s_score desc) pm) where pm = 1
你是要查询每个学生所有课程的总学分是多少? 还是什么? 你这题目不清晰 , 表达的, 叫人好难回答啊!假设:题目是 查询每个学生所有课程的总学分,还有学号唯一select s_id,s_name,sum(s_score) total_score from stu group by s_id,s_name order by s_id
select * from (select s_id,s_name,s_cname,s_score from stu where s_cname ='英语' order by s_score desc) where rownum =1 union select * from (select s_id,s_name,s_cname,s_score from stu where s_cname ='物理'order by s_score desc) where rownum =1 union select * from (select s_id,s_name,s_cname,s_score from stu where s_cname ='数学'order by s_score desc) where rownum =1;
select * from (select s.*, dense_rank() over(partition by s.s_cname order by s_score desc) pm) where pm = 1
还是什么?
你这题目不清晰 ,
表达的,
叫人好难回答啊!假设:题目是 查询每个学生所有课程的总学分,还有学号唯一select s_id,s_name,sum(s_score) total_score from stu group by s_id,s_name order by s_id
先按照每个学生的学生id进行分组,求出每个学生的总分,这是一个子查询。然后在子查询中,找到最大的值对应的学生id就行了
然后再选题第一名!
你可以先用两个SQL语句写!然后合并
(select s_id,s_name,s_cname,s_score from stu where s_cname ='英语' order by s_score desc)
where rownum =1
union
select * from
(select s_id,s_name,s_cname,s_score from stu where s_cname ='物理'order by s_score desc)
where rownum =1
union
select * from
(select s_id,s_name,s_cname,s_score from stu where s_cname ='数学'order by s_score desc)
where rownum =1;