mysql - 带有计数操作的 SQL 连接查询

Question

大家好，我有一个包含下表的数据库

学生表:

+------------+-----------+----------+------------+
| Studeni_ID | Firstname | Lastname | Contact    |
+------------+-----------+----------+------------+
|          1 | Deen      | Nute     |   85124512 |
|          2 | Helen     | Jude     | 2147483647 |
|          3 | Howard    | Mindy    |    8965123 |
+------------+-----------+----------+------------+

first_exam 表:

+------------+--------------+-------+------+------+
| Studeni_ID | Subject_Name | Score | Year | Term |
+------------+--------------+-------+------+------+
|          1 | English      |    54 | 2015 |    1 |
|          1 | Math         |    48 | 2014 |    2 |
|          1 | History      |    85 | 2015 |    1 |
|          2 | English      |    59 | 2015 |    1 |
|          2 | Math         |    65 | 2015 |    1 |
|          3 | English      |    59 | 2015 |    1 |
|          2 | science      |    41 | 2015 |    1 |
+------------+--------------+-------+------+------+

last_exam 表:

+------------+--------------+-------+------+------+
| Studeni_ID | Subject_Name | Score | Year | Term |
+------------+--------------+-------+------+------+
|          1 | English      |    75 | 2015 |    1 |
|          2 | English      |    86 | 2015 |    1 |
|          3 | History      |    72 | 2015 |    1 |
+------------+--------------+-------+------+------+

我正在尝试获取每个学生在 2015 年和第 1 学期参加的所有考试的数量，所以这里是我尝试过的查询及其输出:

SELECT First_Exam.Studeni_ID, student.Firstname, student.Lastname, 
COUNT(First_Exam.Subject_Name)AS FirstE,
COUNT(Last_Exam.Subject_Name)AS LAST_E 
FROM student,First_Exam,Last_Exam  
WHERE student.Studeni_ID=First_Exam.Studeni_ID=Last_Exam.Studeni_ID 
AND First_Exam.Year=2015 AND First_Exam.Term=1 
AND Last_Exam.Year=2015 AND Last_Exam.Term=1 
GROUP BY First_Exam.Studeni_ID;

输出:

+------------+-----------+----------+--------+--------+
| Studeni_ID | Firstname | Lastname | FirstE | LAST_E |
+------------+-----------+----------+--------+--------+
|          1 | Deen      | Nute     |      2 |      2 |
|          2 | Helen     | Jude     |      3 |      3 |
|          3 | Howard    | Mindy    |      1 |      1 |
+------------+-----------+----------+--------+--------+

和

SELECT First_Exam.Studeni_ID, student.Firstname, student.Lastname, COUNT(First_Exam.Subject_Name)AS First_E,COUNT(Last_Exam.Subject_Name)AS Last_E FROM First_Exam 
INNER JOIN student 
ON student.Studeni_ID=First_Exam.Studeni_ID 
INNER JOIN Last_Exam 
ON First_Exam.Studeni_ID=Last_Exam.Studeni_ID 
WHERE First_Exam.Year=2015 AND First_Exam.Term=1 AND Last_Exam.Year=2015 AND Last_Exam.Term=1 GROUP BY First_Exam.Studeni_ID ;

输出:

+------------+-----------+----------+---------+--------+
| Studeni_ID | Firstname | Lastname | First_E | Last_E |
+------------+-----------+----------+---------+--------+
|          1 | Deen      | Nute     |       2 |      2 |
|          2 | Helen     | Jude     |       3 |      3 |
|          3 | Howard    | Mindy    |       1 |      1 |
+------------+-----------+----------+---------+--------+

他们的输出都有错误，谁能帮我解决这个问题....

Answer 1

我加入学生表和第一次考试表，只有当年份和学期如您所说时才对行求和，这将保证学生表中的所有学生都会来，即使他们没有进行任何测试。然后我会用上次考试的相同格式合并它。

为什么有2张结构相同的表，可以放在同一张表中。

然后最后我将两个表的联合值按学生 id 分组求和。

下面的查询应该有效

select Studeni_ID, sum(cnt) FROM (
select Studeni_ID , SUM(case when year = 2015  then 1 else 0 end) cnt FROM students left join first_exam on students.Studeni_ID  = first_exam.Studeni_ID group by students.Studeni_ID
union
select Studeni_ID , SUM(case when year = 2015  then 1 else 0 end) cnt FROM students left join last_exam on students.Studeni_ID  = last_exam.Studeni_ID group by students.Studeni_ID
) group by Studeni_ID