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);要求如下:
列出所有人数大于等于28的系的编号和名称我的解法： 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
);但是只出现一个计算机系，在子查询里面都有三个的，不知道是为什么？不用in的话 怎么解？
where deptment.dept_name in(
select a.dept_name from class_table a
group by a.dept_name
having sum(person_num)>28
);
不用in，用连接或exists吧select d.dept_id,d.dept_name
from department d,(select dept_name from class_table group by dept_name having sum(person_num)>28) t
where d.dept_name=t.dept_name;