select a.*
from teacher a inner join select teacherid,count(1) [数量] from  student group by teacherid) b on a.id=b.teacherId
order by b.[数量]

------解决方案--------------------
SQL code


;WITH c1 AS
(
select t.id, COUNT(t.id) studentNum
from teacher t
LEFT JOIN student s ON t.id=s.teacherid
GROUP BY t.id
HAVING COUNT(t.id)>2
)
SELECT t.*
FROM c1 
JOIN teacher t ON c1.id = t.id
ORDER BY c1.studentNum ASC

------解决方案--------------------
SQL code

create table teacher
(id int, name varchar(10), sex varchar(4))

insert into teacher
select 1, '王老师', '男' union all
select 2, '李老师', '男' union all
select 3, '余老师', '女' union all
select 4, '张老师', '女'

create table student
(id int, name varchar(10), teacherId int)

insert into student
select 1, '张三', 1 union all
select 2, '李四', 1 union all
select 3, '王五', 2 union all
select 4, '宋六', 1 union all
select 5, '郑七', 3


select row_number() over(order by isnull(s.ct,0),t.sex desc) 'id',
t.name,t.sex
from teacher t
left join 
(select teacherId,count(1) 'ct' 
 from student group by teacherId) s on t.id=s.teacherId
order by isnull(s.ct,0),t.sex desc

/*
id                   name       sex
-------------------- ---------- ----
1                    张老师        女
2                    余老师        女
3                    李老师        男
4                    王老师        男

(4 row(s) affected)
*/