只有两个字段用一个sql语句查询出某个学生的姓

SELECT 学生,成绩, 
(SELECT COUNT(*) FROM 表 WHERE a.成绩<=成绩) AS 排名 
FROM 表 AS a

declare @Course table(name varchar(100),cj int) 
insert into @Course 
select 'a',99 
union all 
select 'b',66 
union all 
select 'c',88 
select * from ( 
select (select 1+count(name) from @Course where cj>t.cj) mc,name,cj 
from @Course t 
) tem 
where name='b'

SELECT name,score, 
(SELECT COUNT(*)+1 FROM course WHERE a.score<score) AS sort 
FROM course AS a order by sort