今天碰到一个排序的问题，求大神指点。。。

Question

学生表结构如下：
CREATE TABLE [dbo].[studentInfo](
[id] [int] IDENTITY(1,1) NOT NULL,
[subject] [varchar](50) NOT NULL,
[studentID] [int] NOT NULL,
[isPass] [int] NOT NULL,
[teacherID] [int] NOT NULL)
老师信息表结构如下：
CREATE TABLE [dbo].[teacherInfo](
[id] [int] IDENTITY(1,1) NOT NULL,
[teacherID] [int] NOT NULL,
[Age] [int] NOT NULL,
[sex] [int] NOT NULL)
现在有两个老师数据：insert into teacherInfo(teacherid,age,sex) values(3,50,0)
insert into teacherInfo(teacherid,age,sex) values(15,35,0)有关于两个学生的6条数据：insert into studentInfo(subject,studentid,ispass,teacherid) values('语文',1,0,3)
insert into studentInfo(subject,studentid,ispass,teacherid) values('数学',1,0,3)
insert into studentInfo(subject,studentid,ispass,teacherid) values('英语',1,0,3)
insert into studentInfo(subject,studentid,ispass,teacherid) values('语文',2,1,15)
insert into studentInfo(subject,studentid,ispass,teacherid) values('数学',2,1,15)
insert into studentInfo(subject,studentid,ispass,teacherid) values('英语',2,1,15)
假设现在是对老师的教学能力进行评价，标准是看他所教的学生3科通过的个数，如果通过个数相同（比如都有没通过的），那就按老师年龄就行排序，最后我想把两个人的排序输出出来。- -，我一开始是按下面的方法来的，with ranking(teachid,ranknum)
as
(
select s.teacherid,row_number() over(order by count(*) desc,sum(t.age) desc) from studentinfo s left join teacherinfo t on s.teacherid=t.teacherid where s.ispass=1 group by s.teacherid
)
select * from ranking
可是这个方法会有一个问题，就是当一个老师的学生都通过，另一个老师的学生都没通过的时候，会有一个老师选不出来，即只有一条数据
说的有点啰嗦，额。。求解答-. -

Answer 1

;WITH  ranking ( teachid, ranknum )
AS 
( 
SELECT   
s.teacherid ,
ROW_NUMBER() OVER ( ORDER BY SUM(ispass) DESC, MAX(t.age) DESC ) --isPass相加(这是关键点),年龄取原始年龄更形象(虽然效果一样)
FROM studentinfo s
        LEFT JOIN teacherinfo t 
ON s.teacherid = t.teacherid
GROUP BY s.teacherid
)
SELECT * FROM  ranking
/*
teachid ranknum
15 1
3 2
*/

Answer 2

谢谢技术大大，完美解决了，今天想了好久这个问题，哈，还想请教一下这个ROW_NUMBER函数在over里面有排序的话，这句话是怎么个执行的顺序

Answer 3

with u as
(select s.teacherid,s.studentid,
        case when exists(select 1 from studentinfo t 
                         where t.teacherid=s.teacherid and t.studentid=s.studentid and t.ispass=0)
             then 0 else 1 end 'ispass'
 from studentinfo s
 group by s.teacherid,s.studentid
)
select u.teacherid,sum(case when u.ispass=1 then 1 else 0 end) 'passqty',v.age
 from u
 inner join teacherInfo v on u.teacherid=v.teacherid
 group by u.teacherid,v.age
 order by sum(case when u.ispass=1 then 1 else 0 end) desc,v.age desc/*
teacherid   passqty     age
----------- ----------- -----------
15          1           35
3           0           50(2 row(s) affected)
*/

Answer 4

#1.先按PARTITION BY分组,如果没有PARTITION BY，就把所有记录当成一组。
#2.按照ORDER BY后面的字段进行排序后,顺序生成NUMBER.
你的SQL分析如下：
--#1.第一步,你的SQL执行结果
SELECT   
    s.teacherid ,
SUM(ispass) AS sumIsPass,
MAX(t.age) AS maxAge
FROM studentinfo s
    LEFT JOIN teacherinfo t 
        ON s.teacherid = t.teacherid
GROUP BY s.teacherid--#2.第二步,执行结果
SELECT   
    teacherid ,
ROW_NUMBER() OVER (ORDER BY sumIsPass DESC, maxAge DESC )
FROM
(
SELECT   
s.teacherid ,
SUM(ispass) AS sumIsPass,
MAX(t.age) AS maxAge
FROM studentinfo s
LEFT JOIN teacherinfo t 
ON s.teacherid = t.teacherid
GROUP BY s.teacherid
) t--上面的两步合并成一步,就写成了下面写法
SELECT   
    s.teacherid ,
    ROW_NUMBER() OVER (ORDER BY SUM(ispass) DESC, MAX(t.age) DESC )
FROM studentinfo s
    LEFT JOIN teacherinfo t 
        ON s.teacherid = t.teacherid
GROUP BY s.teacherid