表如下:
表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
表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
解决方案 »
- ORACLE 进入间置的执行处理
- 数据库链问题
- 谁能救救我啊,一个oracle课设,实在不会做了
- 如何消除OEM中ORA-28000: the account is locked 的提示?
- 关于裸设备与数据库创建
- select ltrim('abc_abc','_') from dual;
- 在Oracle中如何创建Job,系统配置有无要求。(Oracle 9i+Win2000 Server)
- 多个数据库的字符集问题
- 老师让建一个connect的服务,不是用easy config建么?在线等待
- oracle多个查询结果如何弄到excel里
- 怎么最快速的查找一个表里有,但另一个表里没有的数据?
- 这样设计的数据库,请给个意见
,(select count(classname) from b where b.student in(select student from a alias2 where alias2.teacher=a.teacher) )
from a
group by student
)
where a.student = c2
group by a.teacher
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
group by student
)
where a.student = c2(+)
group by a.teacher
(select teacher ,tablea.student as student ,class from
tableb,tablea where tablea.student=tableb.student(+))a
group by a.teacher;这是我的sql 呵呵也通过了