例
表class
字段有 姓名(name),成绩(Nnumber)
select name,Nnumber from class ORDER BY Nnumber DESC
显示结果为
姓名 成绩 成绩差
小王 70 0
小李 88 18
小张 99 10
上面的成绩差怎样在select语名中体现出来,
表class
字段有 姓名(name),成绩(Nnumber)
select name,Nnumber from class ORDER BY Nnumber DESC
显示结果为
姓名 成绩 成绩差
小王 70 0
小李 88 18
小张 99 10
上面的成绩差怎样在select语名中体现出来,
如果是我就有办法,不然就帮不了你
小李成差=小李成绩-小王成绩
小张成差=小张成绩-小李成绩
when 0<(select Count(*) from
(select name,Nnumber from class ORDER BY Nnumber ASC) a
where a.Nnumber<b.Nnumber)
then
b.Nnumber-(select max(Nnumber) from
(select name,Nnumber from class ORDER BY Nnumber ASC) a
where a.Nnumber<b.Nnumber)
else
0
end
(select name,Nnumber from class ORDER BY Nnumber ASC) b
TO:楼主 问题已经解决了
select b.name 姓名,b.Nnumber 分数,成绩差=case
(select Count(*) from
(select name,Nnumber from Class) a where a.Nnumber<b.Nnumber)
when 0 then
0
else
b.Nnumber-(select max(Nnumber) from
(select name,Nnumber from Class) a
where a.Nnumber<b.Nnumber)
end
from (select name,Nnumber from Class) b
order by Nnumber asc