2021-08-05查询排序分页语句

-- ========================分页limit和排序order by================================

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = sub.subjectno
WHERE subjectname = 'C语言-1'
ORDER BY studentresult ASC

-- 分页limit

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = sub.subjectno
WHERE subjectname = '高等数学-2'
ORDER BY studentresult ASC
LIMIT 0,5

-- 查询 高等数学-2 课程成绩排名前十的学生，并且分数要大于80 的学生信息（学号，姓名，课程，分数）


SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
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
ORDER BY studentresult DESC
LIMIT 0,10

-- ========================子查询 where语句嵌套一个子查询语句==================
-- 方式一
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname`='高等数学-2'
ORDER BY studentresult DESC
-- 方式二
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result` 

WHERE subjectno =(
SELECT subjectno FROM `subject` WHERE `subjectname`='高等数学-2'
)

ORDER BY studentresult DESC

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

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'

)

 
-- 在这个基础上增加科目  高等数学-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'
 )
)