mysql - SQL 如何将: from two tables that has 2 same data in other named columns,其中一个需要统计

Question

你好。我不擅长组合和索引 SQL 数据库中两个或多个表中的数据，我想学习它。我需要帮助，我的建模情况是这样的:我有两张 table :

学生:

ID, FIRST_NAME, LAST_NAME,DATE_OF_BIRTH, CLASS,
1, Petr, Fish, 4.3.1984, 4A
2, John, Stone, 5.5.1990, 4A
3, Jack, Clark, 6.3.1983, 4A
4, George, Field, 5.8.1955, 4A
5, John, Rock, 6.1.1999, 4B
6, Patrick, Light, 1.11.1983, 4B

和

POINTS_MATH:

STUDENT_ID, POINTS
1, 15
1, 20
2, 30
3, 11
3, 5
4, 3

第一个表中的 ID 和第二个表中的 STUDENTS_ID 表示相同的值，因为必须对具有相同 STUDENTS_ID 的结果行进行计数并分配给正确的 ID我需要结果表:

ID、FIRST_NAME、LAST_NAME、DATE_OF_BIRTH、类(class)(按 STUDENTS_ID 计算的 POINTS 总和)

如果a)可能会看到:

ID, FIRST_NAME, LAST_NAME,DATE_OF_BIRTH, CLASS, (sum of points by ID(STUDENTS_ID))
1, Petr, Fish, 4.3.1984, 4A, 35
2, John, Stone, 5.5.1990, 4A, 30
3, Jack, Clark, 6.3.1983, 4A, 16
4, George, Field, 5.8.1955, 4A, 3

如果 b) 也出现“无分”结果，可能会看到:

ID, FIRST_NAME, LAST_NAME,DATE_OF_BIRTH, CLASS, (sum of points by ID(STUDENTS_ID))
1, Petr, Fish, 4.3.1984, 4A, 35
2, John, Stone, 5.5.1990, 4A, 30
3, Jack, Clark, 6.3.1983, 4A, 16
4, George, Field, 5.8.1955, 4A, 3
5, John, Rock, 6.1.1999, 4B, unknown
6, Patrick, Light, 1.11.1983, 4B, unknown

还有一个问题:在数据量很大的情况下，如何使用哪种索引来进行更有效的查询？非常感谢您的每一个回答。

Answer 1

对于左连接，请使用以下索引，这将有助于更快地获取结果

 create index index1 on POINTS_MATH(STUDENT_ID)

使用下面的查询来获取您想要的结果

select 
t.ID as ID,
t.FIRST_NAME as FIRST_NAME,
t.LAST_NAME as , 
t.DATE_OF_BIRTH as DATE_OF_BIRTH, 
t.CLASS as CLASS, 
case when t.SUM is null then 'unknown' else t.SUM end as SUM_OF_POINTS 
FROM
(SELECT ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, CLASS, SUM(POINTS) as SUM
FROM STUDENTS S LEFT JOIN POINTS_MATH P
ON S.ID = P.STUDENT_ID
GROUP BY ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, CLASS) t