1. Create the table first

CREATE TABLE IF NOT EXISTS  student(
   id varchar(20),-- number
   class varchar(20),-- grade
   score int-- fraction
);

2. Insert related data

INSERT INTO student VALUES ('1',' Class one ',88);
INSERT INTO student VALUES ('2',' Class one ',100);
INSERT INTO student VALUES ('3',' Class one ',65);
INSERT INTO student VALUES ('4',' Class one ',78);
INSERT INTO student VALUES ('5',' Class one ',36);
INSERT INTO student VALUES ('6',' Class two ',88);
INSERT INTO student VALUES ('7',' Class two ',82);
INSERT INTO student VALUES ('8',' Class two ',96);
INSERT INTO student VALUES ('9',' Class two ',53);
INSERT INTO student VALUES ('10',' Class two ',72);
INSERT INTO student VALUES ('11',' Class three ',88);
INSERT INTO student VALUES ('12',' Class three ',66);
INSERT INTO student VALUES ('13',' Class three ',89);
INSERT INTO student VALUES ('14',' Class three ',99);
INSERT INTO student VALUES ('15',' Class three ',56);
INSERT INTO student VALUES ('16',' Class three ',72);

3. query

SELECT
    a.class,
    a.score
FROM
    student a
WHERE
    (
        SELECT
            count(*)
        FROM
            student
        WHERE
            a.class = class
        AND a.score < score
    ) < 3
ORDER BY
    a.class,
    a.score DESC;

result

analysis : Find out the top three in each class How many students have exceeded their scores in their own classes , And then we screen out less than three

            where Medium select It's a guarantee : Traverse all records , Compare each record with the current record , Only when student The same class in the table does not exceed 3 Personal score    
          When you are taller than yourself , This student is in the top three . In other words, the number of people with higher scores in this class is queried in brackets .

 

Technology
©2019-2020 Toolsou All rights reserved,
Send love - A little romance for programmers VHDL—— Design of frequency divider Python Implementation of Hanoi Tower code It's over , Starting salary 30khtml+css+js Make a simple website home page QQ Login interface implementation Hill sorting of sorting algorithm ——c++ realization 【 Wechat applet learning 】 Netease music cloud code page implementation details Resume the 13th session python Blue Bridge Cup 2022 Solution to the 13th Blue Bridge Cup ( whole )