sql技巧-每个班年龄排前两名的人

MySQL 8 及以上版本可以用下面sql语句:

SELECT 
    class_id,
    student_name,
    age
FROM (
    SELECT 
        class_id,
        student_name,
        age,
        -- 按班级分组，按年龄降序排序，年龄相同按学号升序（或其他字段）
        ROW_NUMBER() OVER (
            PARTITION BY class_id 
            ORDER BY age DESC, student_id ASC
        ) AS rn    
    FROM students) AS ranked
WHERE rn <= 2; -- 取每班前两名

mysql5.6及以下版本

1、使用子查询

SELECT 
    s1.class_id,
    s1.student_name,
    s1.age
FROM students s1
WHERE (
    SELECT COUNT(DISTINCT s2.age) 
    FROM students s2 
    WHERE s2.class_id = s1.class_id 
      AND s2.age >= s1.age) <= 2
ORDER BY s1.class_id, s1.age DESC, s1.student_id;

2、使用union all

-- 第一名（包含并列）
SELECT * FROM students s1
WHERE age = (
    SELECT MAX(age) 
    FROM students s2 
    WHERE s2.class_id = s1.class_id)
UNION ALL
-- 第二名（排除第一名后取最高）
SELECT * FROM students s1WHERE age = (
    SELECT MAX(age) 
    FROM students s2 
    WHERE s2.class_id = s1.class_id 
      AND age < (
          SELECT MAX(age) 
          FROM students s3 
          WHERE s3.class_id = s1.class_id
          )
)
ORDER BY class_id, age DESC;

3、使用一个临时表

最后这个方法是新增个字段各班年龄排序字段rank，可以查询

select * from students where rank<=2