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

今天碰到一个排序的问题，求大神指点。。。
学生表结构如下：

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

可是这个方法会有一个问题，就是当一个老师的学生都通过，另一个老师的学生都没通过的时候，会有一个老师选不出来，即只有一条数据。。。
说的有点啰嗦，额。。求解答-. -
[解决办法]

;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
/*
teachidranknum
151
32
*/

[解决办法]

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)
*/

查看更多 下一篇