答山河 mysql 练习题：查询和"01"号的同学学习的课程完全相同的其他同学的信息

[文盲老顾]

-- use mysql经典50习题;
-- 1、学生表
-- Student(SID,Sname,Sage,Ssex)
-- --SID 学生编号,Sname 学生姓名,Sage 年龄,Ssex 学生性别
CREATE TABLE Student(
Sid VARCHAR(20),
Sname VARCHAR(20) NOT NULL DEFAULT '',
Sage VARCHAR(20) NOT NULL DEFAULT '',
Ssex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(Sid)
);

insert into Student values('01' , ' 赵 雷 ' , '1990-01-01' , ' 男 ');
insert into Student values('02' , ' 钱 电 ' , '1990-12-21' , ' 男 ');
insert into Student values('03' , ' 孙 风 ' , '1990-05-20' , ' 男 ');
insert into Student values('04' , ' 李 云 ' , '1990-08-06' , ' 男 ');
insert into Student values('05' , ' 周 梅 ' , '1991-12-01' , ' 女 ');
insert into Student values('06' , ' 吴 兰 ' , '1992-03-01' , ' 女 ');
insert into Student values('07' , ' 郑 竹 ' , '1989-07-01' , ' 女 ');
insert into Student values('08' , ' 王 菊 ' , '1990-01-20' , ' 女 ');

-- 2.课程表
create table Course(Cid int primary key auto_increment,Cname char(20),Tid int);
insert into Course(Cid,Cname,Tid) values(1,'语文',2),(2,'数学',1),(3,'英语',3);

-- 3.教师表
-- Teacher(Tid,Tname)
-- Tid 教师编号,Tname 教师姓名
create table Teacher(Tid int primary key auto_increment,Tname char(20));
insert into Teacher(Tid,Tname) values(1,'张三'),(2,'李四'),(3,'王五');

-- 4.成绩表

-- Score(Sid,Cid,score)
-- --Sid 学生编号,Cid 课程编号,score 分数
CREATE TABLE Score (
Sid INT NOT NULL,
Cid INT NOT NULL,
score INT,
PRIMARY KEY (Sid, Cid)
);
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
-- 解答
select u.*
from (
select sid,GROUP_CONCAT(distinct cid order by cid) lst
from score s
group by sid
) a
inner join (
select sid,GROUP_CONCAT(distinct cid order by cid) comp
from score
where sid='01'
) b on a.lst=b.comp and a.sid!=b.sid
left join student u on a.sid=u.sid

[以山河作礼。]

哈哈哈哈哈，可以，赞