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

[iugo]

比如问题是: 成绩达到过 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;

[dingz]

select count(*) from test_student WHERE studentID IN (select studentID FROM test_scoresheet where score>80)

[akira]

对 studentID 做去重 汇总就好了啊 , test_students 表都不需要

[iugo]

@dingz 如果 test_scoresheet 量级是百万, 不知道哪种做法效率更高.

[iugo]

@akira 是我的例子不够好, 实际上是需要统计 test_students 中的一些数据的, 不是简单地记数.

[akira]

@iugo 那就是 1l 的写法，加个 distinct，或者考虑在内嵌个 max 子查询.

如果你的写法 没有出现性能问题，其实也不需要去管太多的，
能出正确结果的 sql 就是好 sql