SQL：关联查询问题！

来源：百度知道 编辑：UC知道 时间：2024/09/19 16:05:47
有表：student(stu_id,stu_name,stu_sex,cls_id); cls_id是外键关
表：class(cls_id,cls_name);
要求： 求出 女生数 大于20 的班级 的SQL 语句
补充：stu_sex ='f' 女
stu_sex='m' 男

select a.* from class a ,
(select cls_id,count(*) as fcount from student where stu_sex = 'f' group by cls_id) b
where b.cls_id = a.cls_id and b.fcount>20

上面的sql中的标点符号可能不对，直接copy可能用不了sorry

select class.cls_name
from (select count(stu_id),cls_id
from student
where stu_sex ='f'
group by cls_id)as temp(value1,value2),class
where temp.value1>20 and temp.value2=class.cls_id

select class.cls_id from class,student
where student.stu_sex='f' and student.stu_id=class.cls_id
group by class.cls_id
having counts(class.cls_id)>20