每个科目最高的成绩以及对应的同学和科目

数据准备

CREATE TABLE "public"."gis_student" (
  "name" varchar(255),
  "subject" varchar(255),
  "score" numeric(10,2)
);

INSERT INTO "public"."gis_student" ("name", "subject", "score") VALUES ('小明', '语文', '66.00');
INSERT INTO "public"."gis_student" ("name", "subject", "score") VALUES ('小明', '数学', '67.00');
INSERT INTO "public"."gis_student" ("name", "subject", "score") VALUES ('小明', '英语', '69.00');
INSERT INTO "public"."gis_student" ("name", "subject", "score") VALUES ('小红', '语文', '65.00');
INSERT INTO "public"."gis_student" ("name", "subject", "score") VALUES ('小红', '数学', '99.00');
INSERT INTO "public"."gis_student" ("name", "subject", "score") VALUES ('小红', '英语', '88.00');
INSERT INTO "public"."gis_student" ("name", "subject", "score") VALUES ('小强', '语文', '60.00');
INSERT INTO "public"."gis_student" ("name", "subject", "score") VALUES ('小强', '数学', '72.00');
INSERT INTO "public"."gis_student" ("name", "subject", "score") VALUES ('小强', '英语', '92.00');

SQL语句

我们需要选取出每个科目最高的成绩以及对应的同学和科目
SELECT
t1.name,
t1.subject,
t1.score 
FROM
(
    SELECT 
        t0.name,
        t0.subject,
        t0.score,
        ROW_NUMBER() OVER ( PARTITION BY t0.name ORDER BY t0.score DESC ) AS rn 
    FROM
    gis_student t0 
) t1 
WHERE
t1.rn = 1