求写一句sql语句解决方案_Sql Server

求写一句sql语句
现有表A(SNO,POINT) 主键为SNO
B(SNO,CNO,TNO) 主键为(SNO,CNO,TNO)

需求：输出相同CNO和TNO对应的SNO的POINT排名
A
SNO POINT
S1 3.0
S2 4.0
S3 3.7

B
SNO CNO TNO
S1 C1 T1
S2 C1 T1
S3 C1 T1
S1 C2 T1
S2 C2 T1

输出为
CNO TNO RANK
C1 T1 3
C2 T1 2

------解决方案--------------------

SQL code

--> 测试数据：[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 allselect 'S2',4.0 union allselect '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 allselect 'S2','C1','T1' union allselect 'S3','C1','T1' union allselect 'S1','C2','T1' union allselect '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] binner join [A1] a on a.SNO=b.SNO)twhere SNO='S1'/*CNO    TNO    rankC1    T1    3C2    T1    2*/--这样就OK了吧
------解决方案--------------------
估计TravyLee要无语了，正常人都会被LZ搞疯的，呵呵。。。