qq_29354645
青灯烛影
采纳率78.6%
2020-04-21 17:25

求教各位大佬,这个sql,请问这个该怎么写呀!谢谢!

20
已采纳

图片说明

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • weixin_42678268 bj_0163_bj 1年前

    第一题

    select *
    from (
    select class_id,student_id,name,score_sum,rank() over (partition by class_id order by score_sum desc) rw 
    from (
    select CLASS.class_id,STUDENT.student_id,STUDENT.name,sum(score) score_sum
    from CLASS 
    join CLASS_STUDENT
    on CLASS.class_id=CLASS_STUDENT.class_id
    join STUDENT
    on CLASS_STUDENT.student_id=STUDENT.student_id
    join STUDENT_SCORE
    on STUDENT.student_id=STUDENT_SCORE.student_id
    group by CLASS.class_id,STUDENT.student_id,STUDENT.name
    ) t 
    ) t2
    where rw=1
    
    

    第二题

    select *
    from (
    select CLASS.class_id,STUDENT.student_id,STUDENT.name,STUDEN_SCORE.course,score,
    rank() over (partition by class_id,STUDEN_SCORE.course order by score desc) rw 
    from CLASS 
    join CLASS_STUDENT
    on CLASS.class_id=CLASS_STUDENT.class_id
    join STUDENT
    on CLASS_STUDENT.student_id=STUDENT.student_id
    join STUDENT_SCORE
    on STUDENT.student_id=STUDENT_SCORE.student_id
    ) t 
    where rw=1
    
    点赞 1 评论 复制链接分享

相关推荐