更正!上面的错了,不好意思!
select student,sn=
(
select count(*) from tablename
where student=a.student
and
(str(grade,6,2)+course)<=(str(a.grade,6,2)+a.course)
),
course,grade
from tablename a order by student,grade
实现应该是可以的,只是有点麻烦!
关键是把课程名称作为生成编号的一个因素!也就是要把课程名称变成数字!把字符变成数字的方法可能有很多,下面只是我的一种实现方式!
如果课程的个数是确定的,就简单一点,否则复杂一点!
课程个数确定:
select student,sn=
(
select count(*) from
(
select case course when '语文' then 1 when '数学' then 2 when '英语' then 3 else 4 end coursenum,
grade from tablename
) b
where student=a.student and
(b.coursenum+b.grade)<=(a.coursenum+a.grade)),
course,grade
from
(
select case grade when '语文' then 1 when '数学' then 2 when '英语' then 3 else 4 end coursenum,
student,grade,course from tablename
) a
order by student
select student,sn=(select count(distinct 成绩) from tablename where student=a.student and grade<=a.grade),course,grade from tablename a order by student,grade