与 mysql 声明,三个表相混淆

标签: MySQL PHP
发布时间: 2017/4/9 21:58:01
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

得此打印出来作为一个矩阵 3 不同表中的表数据的问题。搜索互联网之后, 我最后找到了解决我的问题。但我也不知道,mysql 声明是如何工作。这是我想要做一个例子

而且我认为我找到了解决方法所以是否有人能帮助解释我它是如何工作的?

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 |
+---------------+------------+------------+-------+

解决方法 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

这将只会返回结果为 students 有记录 scores 表。如果您想要列出所有的学生,你得改变 INNER JOINLEFT OUTER JOIN

更新

我已经更新查询和删除第一个子查询

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

如果这不需要 student_idact_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

解释

JOIN之间 studentscores 为所有学生获取所有的得分 (我添加了另一名学生 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 在列 'activity 1',同样我们添加列' activity 2' 中的分数如果 act_id=2 和最后我们用总和从子查询作为总计

我希望这能阐明你的问题

官方微信
官方QQ群
31647020