用mysql查询各班前3名成绩

傷城~ 2022-05-13 14:48 158阅读 0赞

CREATE TABLE sc
    
     
    
    (
    
     
    
      id    INT,
    
     
    
      name  VARCHAR(20),
    
     
    
      class VARCHAR(20),
    
     
    
      score INT
    
     
    
    );
    
     
    
     
    
     
    
    INSERT INTO sc VALUES (11, 'zx', '一年1班', 68);
    
     
    
    INSERT INTO sc VALUES (12, 'zx1', '一年1班', 98);
    
     
    
    INSERT INTO sc VALUES (13, 'zx2', '一年1班', 58);
    
     
    
    INSERT INTO sc VALUES (14, 'zx3', '一年1班', 48);
    
     
    
    INSERT INTO sc VALUES (15, 'zx4', '一年2班', 88);
    
     
    
    INSERT INTO sc VALUES (16, 'zx5', '一年2班', 68);
    
     
    
    INSERT INTO sc VALUES (17, 'zx6', '一年2班', 98);
    
     
    
    INSERT INTO sc VALUES (18, 'zx7', '一年3班', 78);
    
     
    
    INSERT INTO sc VALUES (19, 'zx8', '一年2班', 58);
    
     
    
    INSERT INTO sc VALUES (20, 'zx9', '一年1班', 88);
    
     
    
    INSERT INTO sc VALUES (21, 'zx10', '一年3班', 83);
    
     
    
    INSERT INTO sc VALUES (22, 'zx11', '一年3班', 82);
    
     
    
    INSERT INTO sc VALUES (23, 'zx12', '一年3班', 81);
    
     
    
     
    
     
    
    SELECT
    
     
    
      s1.name,
    
     
    
      s1.class,
    
     
    
      s1.score
    
     
    
    FROM sc s1
    
     
    
    WHERE
    
     
    
      (
    
     
    
        SELECT count(1)
    
     
    
        FROM sc s2
    
     
    
        WHERE
    
     
    
          s1.class = s2.class AND s2.score >= s1.score
    
     
    
      ) <= 3
    
     
    
    ORDER BY s1.class, s1.score DESC;

优化：使用存储过程或函数来分批查询