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
算了!我投降!! 如果是在Oracle中就好了哈1!它有一个RowId()
有学号的话 1句话so easy 否则的话 2句话 so easy
实现应该是可以的,只是有点麻烦! 关键是把课程名称作为生成编号的一个因素!也就是要把课程名称变成数字!把字符变成数字的方法可能有很多,下面只是我的一种实现方式! 如果课程的个数是确定的,就简单一点,否则复杂一点! 课程个数确定: 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(*) 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
否则的话 2句话 so easy
关键是把课程名称作为生成编号的一个因素!也就是要把课程名称变成数字!把字符变成数字的方法可能有很多,下面只是我的一种实现方式!
如果课程的个数是确定的,就简单一点,否则复杂一点!
课程个数确定:
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
就要写个函数代替case语句,可以用unicode()来逐个解析课程的字符,然后相加,就是一个数字了
具体我就不实现了!
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