查询出所有至少选学了4门课程并且成绩没有不及格的学生学号
select sno //学生学号
from sc //选课表
group by sno
having count(cno)>3
EXCEPT
select sno
from sc
where cj<60; //成绩不及格
分组后对表中未参与分组的字段是不是只可以用集合操作?还有没有别的方法,谢谢
我现在还是小菜,迫切需求支持,谢谢
select sno //学生学号
from sc //选课表
group by sno
having count(cno)>3
EXCEPT
select sno
from sc
where cj<60; //成绩不及格
分组后对表中未参与分组的字段是不是只可以用集合操作?还有没有别的方法,谢谢
我现在还是小菜,迫切需求支持,谢谢
查找两个集合之间不同的项,可以选择保留重复项。语法
Except(«Set1», «Set2»[, ALL])注释
在查找不同的项之前先消除两个集合中的重复项。可选的 ALL 标志保留重复项。清除 «Set1» 中的匹配重复项并保留非匹配重复项。示例
示例Except({Canada, [British Columbia], Mexico, [British Columbia], USA, Washington}, {Canada, Mexico, California})返回{[British Columbia], USA, Washington}示例Except({Canada, [British Columbia], Mexico, [British Columbia], USA, Washington}, {Canada, Mexico, California}, ALL)返回{[British Columbia], [British Columbia], USA, Washington}.
(
select sno //学生学号
from sc //选课表
group by sno
having count(cno) <= 3
unoin
select distinct cno from sc where cj < 60
)
from (
select sno
from sc
group by sno
having count(cno)>3
) a
EXCEPT
select *
from (
select sno
from sc
where cj<60
) b
from (
select sno
from sc
group by sno
having count(cno)>3
) a
EXCEPT
select sno
from sc
where cj<60这样就可以了,第二个查询没有聚合不需要再嵌套
from sc
where sno not in
(select sno
from sc x
group by x.sno
having count(x.cno) <= 3
union
select distinct sno
from sc y
where y.cj < 60);
-- 用子查询
select sno
from sc s
where
(select count(*) from sc where s.sno=sno)>3
and
not exists (select * from sc where s.sno=sno and cj<60)
-->写错了个单词.select * from sc where sno not in
(
select sno //学生学号
from sc //选课表
group by sno
having count(cno) <= 3
union
select distinct cno from sc where cj < 60
)