SQL 一对多关系表的统计 的更优解是什么?

比如问题是: 成绩达到过 80 以上的同学有几位?

示例表:

table test_students name | studentID Jim | 1001 table test_scoresheet studentID | date | score 1001 | 2019-09-24 | 81 1001 | 2019-10-24 | 85 

以 PostgreSQL 为例:

创建测试表及数据:

CREATE TABLE "public"."test_students" ("studentID" serial,"name" text, PRIMARY KEY ("studentID")); INSERT INTO "public"."test_students" ("studentID", "name") VALUES ('1001', 'Jim'); CREATE TABLE "public"."test_scoresheet" ("id" serial,"studentID" int4, "date" text, "score" int4, PRIMARY KEY ("id")); INSERT INTO "public"."test_scoresheet" ("id", "studentID", "date", "score") VALUES ('1', '1001', '2019-09-24', '81'); INSERT INTO "public"."test_scoresheet" ("id", "studentID", "date", "score") VALUES ('2', '1001', '2019-10-24', '85'); 

已知但预计不够良好的解法:

SELECT COUNT(id) FROM ( SELECT DISTINCT ON(test_students."studentID") test_students."studentID" AS id FROM test_students INNER JOIN test_scoresheet ON test_students."studentID" = test_scoresheet."studentID" ) AS some_students;