日期:2014-05-17 浏览次数:20904 次
insert into deptment(dept_id,dept_name)values('1','数学'); insert into deptment(dept_id,dept_name)values('2','计算机'); insert into deptment(dept_id,dept_name)values('3','中文'); insert into deptment(dept_id,dept_name)values('4','经济学'); insert into class_table(class_id,subject_name,dept_name,entertime,person_num)values('101','软件','计算机','1995',20); insert into class_table(class_id,subject_name,dept_name,entertime,person_num)values('102','微电子','计算机','1996',30); insert into class_table(class_id,subject_name,dept_name,entertime,person_num)values('111','无机化学','化学','1995',29); insert into class_table(class_id,subject_name,dept_name,entertime,person_num)values('112','高分子化学','化学','1996',25); insert into class_table(class_id,subject_name,dept_name,entertime,person_num)values('121','统计','数学','1995',20); insert into class_table(class_id,subject_name,dept_name,entertime,person_num)values('141','国际贸易','金融学','1997',30); insert into class_table(class_id,subject_name,dept_name,entertime,person_num)values('131','现代汉语','中文','1996',20); insert into class_table(class_id,subject_name,dept_name,entertime,person_num)values('151','国际汉语','金融学','1996',14);
select deptment.dept_id,deptment.dept_name from deptment where deptment.dept_name in( select a.dept_name from class_table a where deptment.dept_name = a.dept_name group by a.dept_name having sum(person_num)>28 );
select deptment.dept_id,deptment.dept_name from deptment where deptment.dept_name in( select a.dept_name from class_table a group by a.dept_name having sum(person_num)>28 );
------解决方案--------------------
还有,depment表里面没有‘化学’,‘金融学’