自连接的一个问题
有一个学生表(student),里面有 学号(student_id) 功课编号(cid) 学生成绩(corde)三个字段.
用一个SQL查询语句得出:每门功课成绩最好的前2名

CREATE TABLE A(STUDENT_ID INT,CID INT ,CORDE INT )


INSERT A VALUES('1' ,'1','99')
INSERT A VALUES( '2' , '1' , '98')
 INSERT A VALUES('3' ,'1', '100')
 INSERT A VALUES('4', '2' , '88')
 INSERT A VALUES('5' ,'2', '87')
INSERT A VALUES('6' ,'2','88')
 INSERT A VALUES('7' , '3' , '99')
INSERT A VALUES('8' , '3' , '88')
INSERT A VALUES('9' ,'3' , '100')

SELECT * FROM A
------------------------------------------------------
SELECT DISTINCT student1.*  
FROM A student1 where student1.student_id IN  
(SELECT TOP 2 A.student_id  
from A
WHERE A.cid = student1.cid  
ORDER BY corde DESC)
-----------------------------

-----------------------------结果一
1 1 99
3 1 100
4 2 88
6 2 88
7 3 99
9 3 100




我没看懂 有语法盲点
(SELECT TOP 2 A.student_id  
from A
WHERE A.cid = student1.cid  
ORDER BY corde DESC)----结果二

结果同  
SELECT top 2 A.student_id  
from A,A B
WHERE A.cid = B.cid  
ORDER BY A.corde DESC ----结果三
吧 得到2个id 3， 3 与  
第一个结果不同啊  

 结果二的A.cid = student1.cid  
是不是表示 A.cid in（student1.cid ）---- A.cid in（1，1，1，2，2，2，3，3，3 ）






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

SQL code

CREATE TABLE A(STUDENT_ID INT,CID INT ,CORDE INT )

INSERT A VALUES('1' ,'1','99')
INSERT A VALUES( '2' , '1' , '98')
INSERT A VALUES('3' ,'1', '100')
INSERT A VALUES('4', '2' , '88')
INSERT A VALUES('5' ,'2', '87')
INSERT A VALUES('6' ,'2','88')
INSERT A VALUES('7' , '3' , '99')
INSERT A VALUES('8' , '3' , '88')
INSERT A VALUES('9' ,'3' , '100')


select STUDENT_ID,CID,CORDE,rn '名次'
from (select row_number() over(partition by CID order by CORDE desc) rn,
* from A) t
where rn<=2

/*
STUDENT_ID  CID         CORDE       名次
----------- ----------- ----------- --------------------
3           1           100         1
1           1           99          2
4           2           88          1
6           2           88          2
9           3           100         1
7           3           99          2

(6 row(s) affected)
*/