SELECT id,姓名 FROM 学生表 WHERE id IN (SELECT ID FROM 学生成绩表 GROUP BY ID HAVING COUNT(*)>4 AND 成绩<60)
select s.id,ss.name --查找学生表中的学生编号和姓名 from student s join studentscore ss on s.id=ss.id --将学生表和学生成绩表按照id进行链接 where ss.score<60 group by s.id having count(s.id)>4
select * from students where id in( select id from stu_coures where grade < 60 group by id having count(*) >=4)
select id,姓名 from 学生表 a where exists(select 1 from 学生成绩表 where a.id=id and 成绩<60 group by id having count(*)>4)
1楼的用in 不好SELECT a.id,a.姓名 FROM 学生表 a, (SELECT ID FROM 学生成绩表 GROUP BY ID HAVING COUNT(*)>4 AND 成绩<60 ) b WHERE a.id = b.id
in、exists 、表连接,从性能角度考虑,表连接较好!
select id,姓名 from 学生表 where exists(select id from 学生成绩表 where 学生表.id=id and 成绩<60 group by id having count(*)>4)
select a.id from 学生表 a,学生成绩表 b where a.id=b.id and b.成绩<60 group by a.id having count(b.科目)>4
FROM 学生表
WHERE id IN
(SELECT ID
FROM 学生成绩表
GROUP BY ID
HAVING COUNT(*)>4
AND 成绩<60)
from student s join studentscore ss on s.id=ss.id --将学生表和学生成绩表按照id进行链接
where ss.score<60
group by s.id
having count(s.id)>4
where id in(
select id from stu_coures where grade < 60
group by id having count(*) >=4)
from 学生表 a
where exists(select 1 from 学生成绩表 where a.id=id and 成绩<60
group by id having count(*)>4)
FROM 学生表 a,
(SELECT ID
FROM 学生成绩表
GROUP BY ID
HAVING COUNT(*)>4
AND 成绩<60
) b
WHERE a.id = b.id
from 学生表
where exists(select id from 学生成绩表 where 学生表.id=id and 成绩<60
group by id having count(*)>4)
from 学生表 a,学生成绩表 b
where a.id=b.id
and b.成绩<60
group by a.id
having count(b.科目)>4