@R星校长

第2关：查询修课相同学生信息

本关任务：根据提供的表和数据，查询与s_id=01号同学学习的课程完全相同的其他同学的信息（学号s_id，姓名s_name，性别s_sex）。

student表数据：

s_id	s_name	s_sex
01	Mia	女
02	Riley	男
03	Aria	女
04	Lucas	女
05	Oliver	男
06	Caden	男
07	Lily	女
08	Jacob	男

course表数据：

c_id	c_name	t_id
01	Chinese	02
02	Math	01
03	English	03

teacher表数据：

t_id	t_name
01	张三
02	李四
03	王五

score表部分数据：

s_id	c_id	s_score
01	01	80
01	02	90
01	03	99
02	01	70
…	…	…

预期输出：

+------+--------+-------+
| s_id | s_name | s_sex |
+------+--------+-------+
| 02   | Riley  | 男    |
| 03   | Aria   | 女    |
| 04   | Lucas  | 女    |
+------+--------+-------+

开始你的任务吧，祝你成功！

七夕快乐

答案：

参考方法一：

select s1.* from student s1 where not exists(
    select * from score sc where sc.s_id='01' and not exists(
        select * from score where score.c_id=sc.c_id and s1.s_id=s_id)
    )and not exists(
        select * from score sc1 where sc1.s_id=s1.s_id and not exists(
            select * from score where c_id=sc1.c_id and s_id='01')
    )
and s1.s_id<>'01';

参考方法二：

select * from student s where s.s_id in 
    (select s_id from score where c_id in 
        (select c_id from score where s_id='01')
        group by s_id having count(*) = (select count(c_id) from score where s_id='01')
    ) and s.s_id!='01' and
(select count(sc.c_id) from score sc where sc.s_id=s.s_id group by sc.s_id)=(select count(c_id) from score where s_id='02'); 

参考方法三：

#使用group_concat()函数创建一个临时的视图
create view temp as (select s_id,group_concat(c_id) as c from score group by s_id );
#然后再判断c的值是否与01相同即可
select * from student where s_id in (select s_id from temp where c=(select c from temp where s_id='01') and s_id<>'01');

参考方法四：

select * from student where s_id in (
    select s_id from score where c_id in (select c_id from score where s_id='01')
        group by s_id having count(*)=(select count(c_id) from score where s_id='01')
    ) and s_id in (
    select s_id from score group by s_id having count(*)=(select count(c_id) from score where s_id='01')
)
#排除01本身
and s_id!='01';

########## End ##########