插入多条数据:
INSERT INTO scores (`StuId`, `Course`, `Scores`)
VALUES
(9, '数学', 78),
(9, '语文', 635),
(9, '英语', 69) ;
INSERT INTO classinfo (`ClassId`, `ClassName`)
VALUES
(1, '数学'),
(2, '语文'),
(3, '英语') ;
INSERT INTO stuinfo (`StuId`, `ClassId`, `StuName`)
VALUES
(7, 3,'七'),
(8, 3, '八'),
(9, 3, '九') ;
#目标:查找每个班的最高分:
SELECT
sm.id,
sm.StuName,
sm.ClassId,
sm.ClassName,
sc.`Course`,
MAX(sc.`Scores`)
FROM
(SELECT
st1.`StuId` AS id,
c1.`ClassId`,
c1.`ClassName`,
st1.`StuName`
FROM
classinfo c1,
stuinfo st1
WHERE c1.`ClassId` = st1.`ClassId`
ORDER BY st1.`StuId`) AS sm
LEFT JOIN scores sc
ON sc.`StuId` = sm.`id`
GROUP BY sm.ClassName ;
#目标:查找每个班的最高分:
SELECT
sm.id,
sm.StuName,
sm.ClassId,
sm.ClassName,
sc.`Course`,
MAX(sc.`Scores`)
FROM
(SELECT
st1.`StuId` AS id,
c1.`ClassId`,
c1.`ClassName`,
st1.`StuName`
FROM
classinfo c1 LEFT JOIN
stuinfo st1
ON c1.`ClassId` = st1.`ClassId` ) AS sm
LEFT JOIN scores sc
ON sc.`StuId` = sm.`ID`
GROUP BY sm.ClassId;
SELECT c.StuId,c.ClassId,c.StuName,c.ClassName,d.Course,MAX(d.Scores) -- 3)显示学生信息
FROM
#111111111111111111111
(SELECT a.StuId,a.ClassId,a.StuName,b.ClassName FROM stuinfo a
LEFT JOIN classinfo b -- 1)首先关联学生表a 和班级表b
ON a.ClassId = b.ClassId) c
LEFT JOIN scores d -- 2)学生和班级的关联表c 再与成绩表d关联
ON c.StuId = d.StuId
GROUP BY c.StuId -- 4)根据班级分组
#22222222222222
SELECT A.StuId,A.ClassId,A.StuName,B.ClassName,MAX(C.Scores),C.Course FROM stuinfo A
LEFT JOIN classinfo B ON A.ClassID=B.ClassId
LEFT JOIN scores C ON A.StuId=C.StuId
GROUP BY B.ClassId