求一个2张表统计sql,先写过各位大虾

Question

表如下：
表A                 表B
------------------- ----------------------
teacher|student     student|classname
------------------- ----------------------
t_a |blue           blue |math
------------------- ----------------------
t_a |red            blue |english
------------------- ----------------------
t_b |green          red |histroy现在要统计出每个teacher有几个学生，及这些学生总共选得课有多少。请教各位大虾了~想得到的结果是
-------------------
teacher|student|classname
-------------------
t_a    |2      |3
-------------------
t_b    |1      |0

Answer 1

select distinct teacher,(select count(student) from a alias1 where a.teacher=alias1.teacher)
,(select count(classname) from b where b.student in(select student from a alias2 where alias2.teacher=a.teacher) )
  from a

Answer 2

select a.teacher,count(c2),sum(c3)  from a,(       select b.student as c2,count(b.classname) as c3 from  b
       group by student
)
where a.student = c2 
group by a.teacher

Answer 3

我测试是成功的，你试试看！~~~ 
create table student(
student varchar2(10),
classname varchar2(10)
);create table teacher(
teacher varchar2(10),
student varchar2(10)            
);insert into student values('blue','math');
insert into student values('blue','english');
insert into student values('red','histroy');insert into teacher values('t_a','blue');
insert into teacher values('t_a','red');
insert into teacher values('t_b','green');commit;-----------------sql---------------------- select zz.ttea as teacher,zz.t_cou as student,sum(yy.s_class) as classname
   from ( 
         select t.teacher ttea,count(t.student) as t_cou
           from teacher t
         group by t.teacher
        )zz,
        (select t.teacher stea,nvl(s.student,t.student) as stu_name,count(s.classname) as s_class
          from teacher t,student s
         where t.student = s.student(+)
         group by t.teacher,t.student,s.student
         )yy
  where zz.ttea = yy.stea
 group by zz.ttea,zz.t_cou;
--------------------result---------------------TEACHER       STUDENT  CLASSNAME
---------- ---------- ----------
t_a                 2          3
t_b                 1          0

Answer 4

不好意思，应该是这样的：（测试通过）select a.teacher,count(a.student),sum(c3)  from a,(       select b.student as c2,count(b.classname) as c3 from  b
       group by student
)
where a.student = c2（+）
group by a.teacher
 

Answer 5

select a.teacher,count(distinct a.student),count(b.classname) from a,b where a.student=b.student(+)  group by a.teacher

Answer 6

select a.teacher,count( distinct student),count(class) from
(select teacher ,tablea.student as student ,class from
tableb,tablea where tablea.student=tableb.student(+))a
group by  a.teacher;这是我的sql 呵呵也通过了