面试mysql问题

[moon411]

数据库：
有四张表，班级表class(classId,className) 学生表student(stuId,classId,stuName),分数表score(courseid ,stuid,score),课程表course(courseid,courseName)。
（1）查询各班各科分数最低的学生学号，姓名，班级名称，科目名称和分数
（2）查询各班各科平均分，展示平均分，班级名称、科目名称
（3）查询classId为‘1’、className为‘1班’的班级中所有学生所有科目的成绩，若有学生缺考，分数表中没有记录时，这些学生信息和科目信息也要查询出来，最终展示学生学号，姓名，科目名称和分数。

[moon411]

求大佬解答谢谢，大致思路有一点但就是总差点什么

[二月十六]

建议楼主把每道题的思路写出来，然后把问题描述一下，这样更能帮助你，这几道题都是常用的聚合函数的应用，比较简单，希望楼主能自己多思考。

[RINK_1]

--问题1

select B.stuid,B.stuname,C.classname,D.coursename,A.score
from score A
join student B on A.stuid=B.stuid
join class C on A.classid=C.classid
join course D on A.courseid=D.course
where not exists (select 1 from from score 
                           join student  on score.stuid=student.stuid
                  where B.classid=student.classid and score.courseid=A.courseid and score.score<A.score)


--问题2

select max(C.classname) as classname,max(D.coursename) as coursename,avg(A.score) as avg_score
from score A
join student B on A.stuid=B.stuid
join class C on A.classid=C.classid
join course D on A.courseid=D.course
group by A.course,B.classid


--问题3

select stuid,stuname,coursename,score
from student A
left join score B on A.stuid=B.stuid
left join course C on B.courseid=C.courseid
left join class D on A.classid=D.classid
where classid='1' and classname='1'

[moon411]

问题1： select st.stuId,st.stuName,c.className,co.courseName,min(s.score) from score s inner join student st on st.stuId = s.stuid inner join class c on c.classId = st.classId inner join course co on co.courseid = s.courseid group by s.courseid,c.classId order by score,c.className 问题2： select c.className,co.courseName,AVG(s.score) from score s inner join student st on st.stuId = s.stuid inner join class c on c.classId = st.classId inner join course co on co.courseid = s.courseid group by s.courseid,c.classId order by score,c.className 问题3： select st.stuId,st.stuName, co.courseName,s.score from student st inner join class c on c.classId = st.classId left join score s on s.stuid = st.stuId left join course co on co.courseid = s.courseid where c.classId = 1

[moon411]

CREATE TABLE `class` ( `classId` int(11) NOT NULL, `className` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL, PRIMARY KEY (`classId`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; INSERT INTO `class` VALUES (1, 'class 1'),(2, 'class 2'),(3, 'class 3'),(4, 'class 4'); CREATE TABLE `course` ( `courseid` int(11) NOT NULL AUTO_INCREMENT, `courseName` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL, PRIMARY KEY (`courseid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic; INSERT INTO `course` VALUES (1, 'yuwen'),(2, 'shuxue'); CREATE TABLE `score` ( `courseid` int(11) NULL DEFAULT NULL, `stuid` int(11) NULL DEFAULT NULL, `score` int(11) NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic; INSERT INTO `score` VALUES (1, 1, 45),(1, 2, 12),(1, 3, 34),(1, 4, 44),(1, 5, 54),(1, 6, 64),(1, 7, 74),(1, 8, 84),(1, 9, 94),(1, 10, 104),(1, 11, 114),(1, 12, 124),(1, 13, 134),(1, 14, 144),(1, 15, 154),(1, 16, 164),(2, 1, 15),(2, 2, 25),(2, 3, 35),(2, 4, 45),(2, 5, 55),(2, 6, 65),(2, 7, 75),(2, 8, 85),(2, 9, 95),(2, 10, 81),(2, 11, 11),(2, 12, 21),(2, 13, 31),(2, 14, 41),(2, 15, 51),(2, 16, 61); CREATE TABLE `student` ( `stuId` int(11) NOT NULL AUTO_INCREMENT, `classId` int(11) NULL DEFAULT NULL, `stuName` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL, PRIMARY KEY (`stuId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic; INSERT INTO `student` VALUES (1, 1, '1xiaoming'),(2, 1, '1xiaohong'),(3, 1, '1kunjia'),(4, 1, '1shewang'),(5, 2, '2poo'),(6, 2, '2lk'),(7, 2, '2quanse'),(8, 2, '2yuan'),(9, 3, '3poo'),(10, 3, '3lk'),(11, 3, '3quanse'),(12, 3, '3yuan'),(13, 4, '4poo'),(14, 4, '4lk'),(15, 4, '4quanse'),(16, 4, '4yuan');

[狂奔的蜗牛已被占用]

问题1：

select st.stuid,st.stuName,cl.className,co.courseName,min(sc.score)
from 
class cl,student st,score sc,course co
WHERE 
sc.stuid=st.stuId AND
st.classId = cl.classId AND
sc.courseid =co.courseid
group by cl.classId,co.courseid


问题2：
select AVG(sc.score),cl.className,co.courseName
from 
class cl,student st,score sc,course co
WHERE 
sc.stuid=st.stuId AND
st.classId = cl.classId AND
sc.courseid =co.courseid
group by cl.classId,co.courseid


问题3 ：
select s.sid,s.sname,ssc.score, cco.courseName from
(select sst.stuid sid , sst.stuName sname 
from student sst,class  ccl
 where sst.classid=ccl.classid and sst.classid='1' and ccl.className='class 1') as s

left join score ssc on ssc.stuid=s.sid
left join course cco on cco.courseid =ssc.courseid

[狂奔的蜗牛已被占用]

引用 6 楼 狂奔的蜗牛已被占用 的回复:

问题1：

select st.stuid,st.stuName,cl.className,co.courseName,min(sc.score)
from 
class cl,student st,score sc,course co
WHERE 
sc.stuid=st.stuId AND
st.classId = cl.classId AND
sc.courseid =co.courseid
group by cl.classId,co.courseid


问题2：
select AVG(sc.score),cl.className,co.courseName
from 
class cl,student st,score sc,course co
WHERE 
sc.stuid=st.stuId AND
st.classId = cl.classId AND
sc.courseid =co.courseid
group by cl.classId,co.courseid


问题3 ：
select s.sid,s.sname,ssc.score, cco.courseName from
(select sst.stuid sid , sst.stuName sname 
from student sst,class  ccl
 where sst.classid=ccl.classid and sst.classid='1' and ccl.className='class 1') as s

left join score ssc on ssc.stuid=s.sid
left join course cco on cco.courseid =ssc.courseid

测试结果： 问题1： 问题2： 问题3：

[bright33234]

（1）
select stuId,stuName,className,courseName,MIN(score)最低分 from class,STUDENT,SCORE,COURSE
WHERE CLASS.CLASSID=STUDENT.CLASS ID AND student.stuid=score.stuid AND score.courseid=course.courseid
GROUP BY stuId,stuName,className,courseName

（2）
select className,courseName,avg(score)平均分 from student,SCORE,COURSE
WHERE student.stuid=score.stuid AND score.courseid=course.courseid
join class
on class.classId=student.classId
GROUP BY className,courseName

（3）
SELECT * FROM
(select stuId,stuName,courseName,score from student,score,course
WHERE student.stuid=score.stuid AND score.courseid=course.courseid)A
LEFT JOIN
(select * from class where classId=1 and className='1班')B
on A.classId=B.classId


是这样吗

[bright33234]

问题1为什么你们不用分组写所有列，不会报错吗，但是我的结果不对