php - 与mysql语句混淆，三张表

Question

所以我遇到了将 3 个不同表中的表数据打印为矩阵的问题。经过互联网搜索后，我终于找到了解决我的问题的方法。但我不知道 mysql 语句是如何工作的。 This is a example that i want to do

我想我找到了一个解决方案，所以是否有人可以帮助我解释它是如何工作的？

SELECT names.codename,
s1.score AS "Score1", s1.comment AS "Comments1",
s2.score AS "Score2", s2.comment AS "Comments2",
SUM(st.score) AS "Total"
FROM students names 
LEFT JOIN scores s1 ON s1.act_id=1 AND names.id=s1.student_id 
LEFT JOIN scores s2 ON s2.act_id=2 AND names.id=s2.student_id 
LEFT JOIN scores st ON names.id=st.student_id
WHERE names.codename <> ''
GROUP BY names.codename
ORDER BY names.codename;

students table:
+----+---------------+
| id | codename      |
+----+---------------+
|  1 | Budy          |
+----+---------------+

assignments table:
+--------+------------+
| act_id | name       |
+--------+------------+
|      1 | Activity 1 |
|      2 | Activity 2 |
+--------+------------+

scores table:
+------------+--------+-------+
| student_id | act_id | score |
+------------+--------+-------+
|          1 |      1 |    10 |
|          1 |      2 |    10 |
+------------+--------+-------+

现在的问题是，我希望将作业列在顶部，并将分数列在姓名旁边。像这样的事情:

+---------------+------------+------------+-------+
| codename      | Activity 1 | Activity 2 | Total |
+---------------+------------+------------+-------+
|          budy |         10 |         10 |    20 |
+---------------+------------+------------+-------+

Answer 1

试试这个

SELECT s.codename, SUM(IF(act.act_id=1, act.score, 0)) AS 'Activity 1', SUM(IF(act.act_id=2, act.score, 0)) AS 'Activity 2', total.score AS 'total' 
FROM
    students s
    INNER JOIN (SELECT student_id, act_id, SUM(score) AS score FROM scores GROUP BY 1, 2) act ON s.id=act.student_id
    INNER JOIN (SELECT student_id, SUM(score) AS score FROM scores GROUP BY 1) total ON s.id=total.student_id
GROUP BY 1

这将仅返回在 scores 表中有记录的学生的结果。如果您想列出所有学生，则必须将 INNER JOIN 更改为 LEFT OUTER JOIN

更新

我已更新查询并删除了第一个子查询

SELECT student_id, act_id, SUM(score) AS score FROM scores GROUP BY 1, 2

如果 student_id 和 act_id 是复合键，则不需要这样做，因此我们可以直接使用 scores 表。这样查询就变成了

SELECT s.codename, SUM(IF(act.act_id=1, act.score, 0)) AS 'Activity 1', SUM(IF(act.act_id=2, act.score, 0)) AS 'Activity 2', total.score AS 'total' 
FROM
    students s
    INNER JOIN scores as 'act' ON s.id=act.student_id
    INNER JOIN (SELECT student_id, SUM(score) AS score FROM scores GROUP BY 1) total ON s.id=total.student_id
GROUP BY 1

说明

student 和 scores 之间的 JOIN 获取所有学生的所有分数(我添加了另一个 ID 为 2)的学生> 并命名 Pal)，例如

+----+----------+------------+--------+-------+
| id | codename | student_id | act_id | score |
+----+----------+------------+--------+-------+
|  1 | Buddy    |          1 |      1 |    10 |
|  1 | Buddy    |          1 |      2 |    10 |
|  2 | Pal      |          2 |      1 |    15 |
|  2 | Pal      |          2 |      2 |    10 |
+----+----------+------------+--------+-------+

子查询

SELECT student_id, SUM(score) AS score FROM scores GROUP BY 1

像这样获取每个学生的分数总和

+------------+-------+
| student_id | score |
+------------+-------+
|          1 |    20 |
|          2 |    25 |
+------------+-------+

我们将结果放在主要的 SELECT 部分中，使用

SELECT s.codename, SUM(IF(act.act_id=1, act.score, 0)) AS 'Activity 1', SUM(IF(act.act_id=2, act.score, 0)) AS 'Activity 2', total.score AS 'total'...

如果act_id=1在“事件1”列中，我们添加分数，类似地，如果act_id=2，我们在“事件2”列中添加分数，最后我们使用子查询的总和为“总计”

我希望这能解答您的疑问