组内排序

CREATE TABLE stu ( id INT, NAME VARCHAR(20), course VARCHAR(20), score INT );
INSERT INTO stu
VALUES (1, 'ls', 'yuwen', 98),
    (2, 'ls', 'shuxue', 78),
    (3, 'ls', 'yingyu', 100),
    (4, 'ww', 'yuwen', 90),
    (5, 'ww', 'shuxue', 100),
    (6, 'ww', 'yingyu', 20);
SELECT NAME, course, score
FROM (
    SELECT NAME, course, score, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY score DESC) AS aa
    FROM stu
) s
WHERE aa = 2;