统计各部门中姓张的人数?
重点:请详细告知您是怎么思考这问题的? 授之以鱼不如授之以渔 懂? O(∩_∩)O~create table student
(
studentid integer,
name varchar2(20),
age integer,
classid integer
)
insert into student values(8101,'张三',18,101);
insert into student values(8101,'张四',15,121);
insert into student values(8101,'钱飞',14,131);
insert into student values(8101,'李飞',13,102);
insert into student values(8101,'王五',12,141);
create table class
(
classid integer,
subject varchar2(20),
deptname varchar2(20),
entertime integer,
num integer
)insert into CLASS (classid, subject, deptname, entertime, num)
values (101, '软件', '计算机', '1995', 20);
insert into CLASS (classid, subject, deptname, entertime, num)
values (102, '微电子', '计算机', '1996', 30);
insert into CLASS (classid, subject, deptname, entertime, num)
values (111, '无机化机', '化学', '1995', 29);
insert into CLASS (classid, subject, deptname, entertime, num)
values (112, '高分子化学', '化学', '1996', 25);
insert into CLASS (classid, subject, deptname, entertime, num)
values (121, '统计', '数学', '1995', 20);
insert into CLASS (classid, subject, deptname, entertime, num)
values (131, '现代语言', '中文', '1996', 20);
insert into CLASS (classid, subject, deptname, entertime, num)
values (141, '国际贸易', '经济学', '1997', 30);
insert into CLASS (classid, subject, deptname, entertime, num)
values (142, '国际金融', '经济学', '1998', 14);create table department
(
departmentid integer,
deptname varchar2(20)
)
insert into DEPARTMENT (departmentid, deptname)
values (1, '数学');
insert into DEPARTMENT (departmentid, deptname)
values (2, '计算机');
insert into DEPARTMENT (departmentid, deptname)
values (3, '化学');
insert into DEPARTMENT (departmentid, deptname)
values (4, '中文');
insert into DEPARTMENT (departmentid, deptname)
values (5, '经济学');
重点:请详细告知您是怎么思考这问题的? 授之以鱼不如授之以渔 懂? O(∩_∩)O~create table student
(
studentid integer,
name varchar2(20),
age integer,
classid integer
)
insert into student values(8101,'张三',18,101);
insert into student values(8101,'张四',15,121);
insert into student values(8101,'钱飞',14,131);
insert into student values(8101,'李飞',13,102);
insert into student values(8101,'王五',12,141);
create table class
(
classid integer,
subject varchar2(20),
deptname varchar2(20),
entertime integer,
num integer
)insert into CLASS (classid, subject, deptname, entertime, num)
values (101, '软件', '计算机', '1995', 20);
insert into CLASS (classid, subject, deptname, entertime, num)
values (102, '微电子', '计算机', '1996', 30);
insert into CLASS (classid, subject, deptname, entertime, num)
values (111, '无机化机', '化学', '1995', 29);
insert into CLASS (classid, subject, deptname, entertime, num)
values (112, '高分子化学', '化学', '1996', 25);
insert into CLASS (classid, subject, deptname, entertime, num)
values (121, '统计', '数学', '1995', 20);
insert into CLASS (classid, subject, deptname, entertime, num)
values (131, '现代语言', '中文', '1996', 20);
insert into CLASS (classid, subject, deptname, entertime, num)
values (141, '国际贸易', '经济学', '1997', 30);
insert into CLASS (classid, subject, deptname, entertime, num)
values (142, '国际金融', '经济学', '1998', 14);create table department
(
departmentid integer,
deptname varchar2(20)
)
insert into DEPARTMENT (departmentid, deptname)
values (1, '数学');
insert into DEPARTMENT (departmentid, deptname)
values (2, '计算机');
insert into DEPARTMENT (departmentid, deptname)
values (3, '化学');
insert into DEPARTMENT (departmentid, deptname)
values (4, '中文');
insert into DEPARTMENT (departmentid, deptname)
values (5, '经济学');
select * from student where name like '张_%'
--先求出每个班级姓张的人数,然后按部门关联到班级,求出每个部门的姓张的学生人数就可以了
select d.deptname,sum(t.cou) cou
from DEPARTMENT d
left join(
select class.classid,min(deptname) deptname,count(student.classid) cou from class
left join student on class.classid =student.classid and name like '张%'
group by class.classid) t
on d.deptname=t.deptname
group by d.deptname;
insert into student values(8101,'张四',15,121);
insert into student values(8101,'钱飞',14,131);
insert into student values(8101,'李飞',13,102);
insert into student values(8101,'王五',12,141);--------------------
学生ID可以重复吗?不是主键
FROM DEPARTMENT C,
(SELECT *
FROM CLASS A,
(SELECT CLASSID, COUNT(1) SHULIANG
FROM STUDENT T
WHERE T.NAME LIKE '张%'
GROUP BY CLASSID) B
WHERE B.CLASSID = A.CLASSID) E
WHERE C.DEPTNAME = E.DEPTNAME(+);