sql 语句问题
现有三张表student(stuno stuname ),class(cid,cname,cteacher),sc(stuno,cid,chengji)查询成绩有两科或两科以上不及格的学生? --------------------编程问答-------------------- select c.stuno , c.stuname from student s inner join (select stuno , count(*) as cnt from bbb where chengji < 60group by stuno ) d on s.stuno = d.stuno where d.cnt >= 2 ; --------------------编程问答-------------------- select c.stuno , c.stuname from student s inner join (select stuno , count(*) as cnt from sc where chengji < 60
group by stuno ) d on s.stuno = d.stuno where d.cnt >= 2 ; --------------------编程问答-------------------- 楼上正解
select stuno , count(*) as cnt from sc where chengji < 60
group by stuno
这条语句查出
stuno cnt(不及格数)
1001 1
1002 2
内连接 inner join s.stuno = d.stuno 用于匹配名字
d.cnt >= 2 则查出。大于2门不及格的where条件 --------------------编程问答-------------------- 你看看我这个吧!
select s.stuname from student s where s.stuno in(select sc.stuno from class c,sc sc group by sc.stuno having sc.cid=c.cid and sc.chengji<60 and count(sc.stuno)>=2);
要查询信息,你可以稍作修改哦 --------------------编程问答--------------------
select a.stuname--------------------编程问答--------------------
from student a
where (select count(cid) from sc where chengji < 60 and stuno=a.stuno) >=2
这个......可以么?没想这样的查询....
补充:Java , Java SE