--> 测试数据：[A1]
if object_id('[A1]') is not null drop table [A1]
create table [A1]([SNO] varchar(2),[POINT] numeric(2,1))
insert [A1]
select 'S1',3.0 union all
select 'S2',4.0 union all
select 'S3',3.7
--> 测试数据：[B2]
if object_id('[B2]') is not null drop table [B2]
create table [B2]([SNO] varchar(2),[CNO] varchar(2),[TNO] varchar(2))
insert [B2]
select 'S1','C1','T1' union all
select 'S2','C1','T1' union all
select 'S3','C1','T1' union all
select 'S1','C2','T1' union all
select 'S2','C2','T1'


select CNO,TNO,[rank] from(
select b.SNO,b.CNO,b.TNO,
RANK()over(partition by b.CNO order by point desc) as [rank] from [B2] b
inner join [A1] a on a.SNO=b.SNO
)t
where SNO='S1'

/*
CNO    TNO    rank
C1    T1    3
C2    T1    2
*/

--这样就OK了吧

------解决方案--------------------
估计TravyLee要无语了，正常人都会被LZ搞疯的，呵呵。。。