mysql分组子查询_MySQL——子查询、分组过滤

1、子查询(Where)

where (这个值是计算出来的)

本质 ：在where语句中嵌套一个子查询语句

-- ================ where =================

-- 1、查询 数据库结构-1 的所有考试结果(学号，科目编号，成绩)，降序排列

-- 方式一： 使用连接查询

SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`

FROM `result` r

INNER JOIN `subject` sub

ON r.SubjectNo = sub.SubjectNo

WHERE SubjectName = '数据库结构-1'

ORDER BY StudentResult DESC

-- 方式二： 使用子查询(由里及外)

-- 查询所有数据库结构-1 的学生学号

SELECT `StudentNo`,`SubjectNo`,`StudentResult`

FROM `result`

WHERE SubjectNo = (

SELECT SubjectNo FROM `subject`

WHERE SubjectName = '数据库结构-1'

)

ORDER BY StudentResult DESC

-- 查询课程为 高等数学-2 且分数不小于 80 的同学的学号和姓名

SELECT s.StudentNo,StudentName

FROM student s

INNER JOIN result r

ON s.StudentNo = r.StudentNo

INNER JOIN `subject` sub

ON r.`SubjectNo` = sub.`SubjectNo`

WHERE `SubjectName` = '高等数学-2' AND StudentResult>=80

-- 分数不小于80分的学生的学号和姓名

SELECT DISTINCT s.`StudentNo`,`StudentName`

FROM student s

INNER JOIN result r

ON r.StudentNo = s.StudentNo

WHERE `StudentResult`>=80

-- 在这个基础上增加一个科目， 高等数学-2

-- 查询 高等数学-2 的编号

SELECT DISTINCT s.`StudentNo`,`StudentName`

FROM student s

INNER JOIN result r

ON r.StudentNo = s.StudentNo

WHERE `StudentResult`>=80 AND `SubjectNo` = (

SELECT SubjectNo FROM `subject`

WHERE `SubjectName` = '高等数学-2'

)

-- 在改造 (由里及外)

SELECT StudentNo,StudentName FROM student WHERE StudentNo IN (

SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo = (

SELECT SubjectNo FROM `subject` WHERE `SubjectName` = '高等数学-2'

)

)

2、分组和过滤

**GROUP BY：**分组

HAVING： 过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80

-- 核心： (根据不同的课程分组)

SELECT SubjectName, AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分

FROM result r

INNER JOIN `subject` sub

ON r.`SubjectNo` = sub.`SubjectNo`

GROUP BY r.SubjectNo -- 通过什么字段来分组

HAVING 平均分>80