用到两个表,teacher,student和graduatedStudent，分别是老师，学生，已毕业学生
teacher的字段和值如下：
id name sex
1 王老师 男
2 李老师 男
3 余老师 女
4 张老师 女
student的字段和值如下：
id name teacherId
1 张三 1
2 李四 1
3 王五 2
4 宋六 1
6 郑七 2
7 郑八 3
8 郑九 3
9 郑十 3
graduatedStudent的字段和值如下：
id time isGraduated
1 2010 true
3 2000 true
4 2003 true
我想查出所有老师的资料，但按照已所教的毕业学生数量由少到多排序。
比如此列就显示为：
id name sex
1 张老师 女
2 余老师 女
3 李老师 男
4 王老师 男
刚才两位兄台懂的哈
谢谢！
------解决方案--------------------
- 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 CREATE TABLE graduatedStudent(id INT, TIME DATE ,isGraduated VARCHAR(10)) INSERT INTO graduatedStudent SELECT 1, '2010', 'true' UNION ALL SELECT 3, '2000' ,'true' UNION ALL SELECT 4, '2003' ,'true' select a.* from teacher a inner join (select teacherid,count(1) [数量] from student a INNER JOIN graduatedStudent b ON a.id=b.id WHERE isGraduated='true' group by teacherid) b on a.id=b.teacherId order by b.[数量] /* id name sex ----------- ---------- ---- 2 李老师 男 1 王老师 男 (2 行受影响) */
------解决方案--------------------
- 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 CREATE TABLE graduatedStudent(id INT, TIME DATETIME ,isGraduated VARCHAR(10)) INSERT INTO graduatedStudent SELECT 1, '2010', 'true' UNION ALL SELECT 3, '2000' ,'true' UNION ALL SELECT 4, '2003' ,'true'select t.*,isnull(rn,0) as rnfromteacher t left join(SELECT teacherId,count(teacherId) as rnfrom student s left join graduatedStudent g on s.id=g.idand isGraduated='true'group by teacherId) r on t.id=r.teacherIdorder by isnull(rn,0)/*id name sex rn ----------- ---------- ---- ----------- 4 张老师 女 02 李老师 男 13 余老师 女 11 王老师 男 3