資料庫：
有四張表，班級表class(classId,className) 學生表student(stuId,classId,stuName),分數表score(courseid ,stuid,score),課程表course(courseid,courseName)。
（1）查詢各班各科分數最低的學生學號，姓名，班級名稱，科目名稱和分數
（2）查詢各班各科平均分，展示平均分，班級名稱、科目名稱
（3）查詢classId為‘1’、className為‘1班’的班級中所有學生所有科目的成績，若有學生缺考，分數表中沒有記錄時，這些學生資訊和科目資訊也要查詢出來，最終展示學生學號，姓名，科目名稱和分數。
uj5u.com熱心網友回復：
求大佬解答謝謝，大致思路有一點但就是總差點什么
uj5u.com熱心網友回復：
建議樓主把每道題的思路寫出來，然后把問題描述一下，這樣更能幫助你，這幾道題都是常用的聚合函式的應用，比較簡單，希望樓主能自己多思考。uj5u.com熱心網友回復：
--問題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'
uj5u.com熱心網友回復：
問題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
uj5u.com熱心網友回復：
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');
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/261934.html
標籤：疑難問題