weixin_42081616
long丶api
采纳率0%
2019-06-18 11:51 阅读 936

怎么用sql语句对学生成绩进行排名?

一张学生成绩表assess_score,
有total_score(总成绩),id,student_name(姓名),student_number(学号),
assess_year(学年),根据总成绩进行成绩排名,需要考虑重复成绩和每个学年的成绩排名

这是我写的 请大佬帮我改一下

SELECT assess_year,student_number,id,SUM(courseScore),DENSE_RANK() OVER(ORDER BY SUM(courseScore) DESC ) as Ranking
            FROM course_score As courseScore group by student_number,assess_year,id
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

4条回答 默认 最新

  • qq_22847605 qq_22847605 2019-06-18 15:54

    SELECT
    assess_year,
    student_number,
    id,
    SUM( courseScore ),
    DENSE_RANK() OVER( partition by student_number,
    assess_year,
    id ORDER BY SUM( courseScore ) DESC ) AS Ranking
    FROM
    course_score AS courseScore

    点赞 1 评论 复制链接分享
  • m0_37574389 有头发的猩猩 2019-06-18 12:50

    SELECT
    SUM( total_score ) score,
    assess_year
    FROM
    course_score
    GROUP BY
    student_number,
    assess_year
    ORDER BY
    score

    点赞 评论 复制链接分享
  • weixin_39946839 猪头,怪我喽 2019-06-18 13:45

    order by 字段名就可以排序了啊

    点赞 评论 复制链接分享
  • 3622691 3622691 2019-06-18 14:13

    SELECT assess_year,student_number,id,SUM(courseScore),DENSE_RANK() OVER(ORDER BY SUM(courseScore) DESC ) as Ranking
    FROM course_score As courseScore group by student_number,assess_year,id
    order by total_score

    点赞 评论 复制链接分享

相关推荐