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,
Forbes China Auto rich list : He xiaopengdi 11 Li Xiangdi 14 Li Bindi 15 Change one's mind ! Tesla starts to deliver made in China to European market Model 3 The difference between memory overflow and memory leak , Causes and Solutions Character recognition technology of vehicle license plate based on Neural Network Vue Transfer parameters and receiving of page Jump SparkSQL Achieve partition overlay write 1190 Reverses the substring between each pair of parentheses leetcode Note 14 : The second biggest obstacle to motivating others in R & D management Chrome OS, For programmers and Windows What does it mean ? Internet Marketing JAVA Convert a string to a numeric type