sql题目

1、不同班级的学生,先按班级分组再身高从低到高排序

1.1 显示每个班最矮的学生

SELECT class_id, student_name, height
FROM (
    SELECT class_id, student_name, height,
           RANK() OVER (PARTITION BY class_id ORDER BY height) as rank_in_class
    FROM students
) AS ranked_students
WHERE rank_in_class = 1;

1.2 列出每个班级的所有学生，并按身高排序

SELECT class_id, student_name, height
FROM students
ORDER BY class_id, height;

1.3 在每个班级内部按身高排序

SELECT class_id, student_name, height
FROM (
    SELECT class_id, student_name, height,
           RANK() OVER (PARTITION BY class_id ORDER BY height) as rank_in_class
    FROM students
) AS ranked_students
ORDER BY class_id, rank_in_class;