数据库学习笔记-09(MySQL )->排序

支持多个字段排序

语法参考：
ORDER BY col_name [ASC|DESC]

排序规则

-- 将三年级所有学生的语文成绩按照从高到低进行排序
SELECT * FROM school_student_grade
WHERE course_id = 1
ORDER BY score DESC;

-- 将三年级所有学生的语文成绩按照从高到低进行排序
SELECT * FROM school_student_grade
WHERE course_id = 1
ORDER BY score DESC;

SELECT t1.stu_no, t1.stu_name, t3.course_name, t3.teacher, t2.score
FROM school_student_info t1, school_student_grade t2, school_course_info t3
WHERE t1.id = t2.student_id AND t2.course_id = t3.id
AND t3.course_name = '语文'
-- ORDER BY score;
ORDER BY score DESC;

-- 先班级，再分数
SELECT t1.class_name, t1.stu_no, t1.stu_name, t3.course_name, t3.teacher, t2.score
FROM school_student_info t1, school_student_grade t2, school_course_info t3
WHERE t1.id = t2.student_id AND t2.course_id = t3.id
AND t3.course_name = '语文'
-- ORDER BY score;
ORDER BY t1.class_name DESC, score DESC;

-- 将三年级所有学生的期末考试总成绩按照从高到低进行排序
SELECT t1.stu_no, t1.stu_name, SUM(t2.score)
FROM school_student_info t1, school_student_grade t2
WHERE t1.id = t2.student_id
GROUP BY t1.stu_no
ORDER BY SUM(t2.score) DESC;
-- 或者是：
SELECT t1.stu_no, t1.stu_name, SUM(t2.score) AS score
FROM school_student_info t1, school_student_grade t2
WHERE t1.id = t2.student_id
GROUP BY t1.stu_no
ORDER BY score DESC;