请问这个子查询语句怎么写呢？
来源：3-1 数学函数的使用
慕妹4386209
2019-01-08 17:08:49
--学生表
CREATE table stu(
id int not null primary key auto_increment,
sname varchar(20) not null
);
--科目表
create table sub(
id int not null primary key AUTO_INCREMENT,
stuId int not null,
scoId int not null,
FOREIGN key(scoId) REFERENCES sco(id),
FOREIGN key(stuId) REFERENCES stu(id)
);
--成绩表
CREATE table sco (
id int not null primary key auto_increment,
score int not null,
subject varchar(20) not null
)
--插入数据
INSERT into stu(sname) values ("张三"),("李四"),("王五");
INSERT into sco(score) values (98),(97),(80),(40),(90),(50),(66),(78);
INSERT into sub(subject,stuId,scoId) values
("语文",1,1),("语文",2,7),("语文",3,6),
("数学",1,4),("数学",2,3),("数学",3,4),
("外语",1,7),("外语",2,4),("外语",3,1);
-- 需求是查询出所有学生的各科目的成绩并统计平均值
4回答
-
好帮手慕阿莹
2019-01-08
是先去查询子查询的：
祝学习愉快。
0 -
慕妹4386209
提问者
2019-01-08
SELECT sname,
(select score from sco inner join sub on scoId=sco.id where subject="语文" and stuId=stu.id) as 语文,
(select score from sco inner join sub on scoId=sco.id where subject="数学" and stuId=stu.id) as 数学,
(select score from sco inner join sub on scoId=sco.id where subject="外语" and stuId=stu.id) as 外语
from stu LEFT join sub on sub.stuid=stu.id
LEFT join sco on sco.id=sub.scoid group by stu.sname;
虽然我是写出来了，但是还是有点懵逼啊，老师能讲一讲这句sql语句执行的过程不，比如说先执行子查询里面的还是说先把
SELECT sname from stu LEFT join sub on sub.stuid=stu.id
LEFT join sco on sco.id=sub.scoid group by stu.sname;
这句执行完再去执行子查询的？
0 -
慕妹4386209
提问者
2019-01-08
SELECT sname,
(select score from sco inner join sub on scoId=sco.id where subject="语文" and stuId=stu.id) as 语文,
(select score from sco inner join sub on scoId=sco.id where subject="数学" and stuId=stu.id) as 数学,
(select score from sco inner join sub on scoId=sco.id where subject="外语" and stuId=stu.id) as 外语
from stu LEFT join sub on sub.stuid=stu.id
LEFT join sco on sco.id=sub.scoid group by stu.sname;
0 -
慕妹4386209
提问者
2019-01-08
成绩表的subject 字段我写错了，sql语句应该是
--学生表
CREATE table stu(
id int not null primary key auto_increment,
sname varchar(20) not null
);
--科目表
create table sub(
id int not null primary key AUTO_INCREMENT,
stuId int not null,
scoId int not null,
subject varchar(20) not null
FOREIGN key(scoId) REFERENCES sco(id),
FOREIGN key(stuId) REFERENCES stu(id)
);
--成绩表
CREATE table sco (
id int not null primary key auto_increment,
score int not null,
)
--需求是查询出所有学生的各科目的成绩并统计平均值
0
相似问题