来看看这个sql语句怎么写

Question

有一学生成绩表，表内现存放本次考试记录和上次考试记录，现须知道各考生本次考试和上次考试的考分之差，并按照考分差值从高到低排列，如成绩如下：学生姓名 考试时间 分数
张1      上次  80
李2      上次  76.5
王1      上次  98
王2      上次  97
王3      上次  99
王4      上次  70
张1      本次  80
李2      本次  75.5
王1      本次  96
王2      本次  93
王3      本次  94
王4      本次  76
查询后结果应该为：
学生姓名 考试时间 分数 上次分数 相差分数
王4      本次      76     70        6
王3      本次      94     99        5
王2      本次      93     97        4
王1      本次      96     98        2
李2      本次      75.5   76.5      1
张1      本次      80     80        0

Answer 1

select a.学生姓名,a.考试时间,a.分数,
b.分数 as 上次分数,
a.分数-b.分数 as 相差分数
from 成绩表 a
left join 成绩表 b on a.学生姓名=b.学生姓名 and b.考试时间='上次'
where a.考试时间='本次'
order by 相差分数 desc

Answer 2

加入自增字段ID：
SELECT a.*,b.分数 as 上次分数,abs(a.分数-b.分数) as  相差分数
FROM aa1 a  
left join aa1 b
on a.学生姓名=b.学生姓名
where a.考试时间='本次' and b.考试时间='上次' order by 3

Answer 3

SELECT a.*,b.分数 as 上次分数,abs(a.分数-b.分数) as  相差分数 
FROM aa1 a,aa1 b  
where a.考试时间='本次' 
and b.考试时间='上次' 
and a.学生姓名=b.学生姓名 
order by 3 

Answer 4

SELECT a.*,b.分数 as 上次分数,abs(a.分数-IFNULL(b.分数,0)) as  相差分数
FROM aa1 a  
left join (SELECT * FROM AA1 WHERE 考试时间='上次') b
on a.学生姓名=b.学生姓名 AND A.ID>B.ID
where a.考试时间='本次'  order by 3