sql 练习查询和“01“号的同学学习的课程完全相同的其他同学的信息

sql练习题 练习查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT  s_id,s_name,s_birth,s_sex
FROM student 
WHERE student.s_id  in(
SELECT s_id FROM score WHERE s_id!='01'AND c_id IN
( SELECT c_id FROM score WHERE  s_id='01')
GROUP BY s_id HAVING COUNT(*) =(SELECT COUNT(*) FROM score WHERE s_id='01'))

分解：

SELECT s_id FROM score WHERE s_id!='01'AND c_id IN
( SELECT c_id FROM score WHERE  s_id='01')

这是查询除了学号01号同学以外的学习了相同课程的同学id*/

GROUP BY s_id 
HAVING COUNT(*) =(SELECT COUNT(*) FROM score WHERE s_id='01'

因为学号为01的同学学了多门课程 因此上个查询的结果是有多个数据 应按照01同学所学课程的数目进行分组