php - 一次 GROUP BY 多个条件

Question

我有一个这样的表:

用户

+----+----------+-------------+                           
| id | name     | other_stuff |                           
+----+----------+-------------+                           
|  1 | John Doe |           x |                           
|  2 | Jane Doe |           y |                           
|  3 | Burt Olm |           z |                           
+----+----------+-------------+                           

地点

+----+------------+-------------+                         
| id | name       | other_stuff |                         
+----+------------+-------------+                         
|  1 | Building A |           x |                         
|  2 | Building B |           y |                         
+----+------------+-------------+                         

主题

+----+------------+-------------+                         
| id | name       | other_stuff |                         
+----+------------+-------------+                         
|  1 |       Math |           x |                         
|  2 |    English |           y |                         
+----+------------+-------------+           

还有一个连接表:

PastLectures = 发生的讲座

+----+-----------+----------+------------+---------+------------+
| id | id_users  | id_place | id_subjects| length  | date       |
+----+-----------+----------+------------+---------+------------+
|  1 |         1 |        1 |          1 |      60 | 2015-10-25 |
|  2 |         1 |        1 |          1 |     120 | 2015-11-06 |
|  3 |         2 |        2 |          2 |     120 | 2015-11-04 |
|  4 |         2 |        2 |          1 |      60 | 2015-11-10 |
|  5 |         1 |        2 |          1 |      60 | 2015-11-10 |
|  6 |         2 |        2 |          1 |      40 | 2015-11-15 |
|  7 |         1 |        2 |          2 |      30 | 2015-11-15 |
+----+-----------+----------+------------+---------+------------+

我想显示给定月份每个用户的所有类(class)的总和。 SUM 应按每个Places 和Subjects 分组。

最终 PHP 输出的结果应如下所示:

November 2015                                             
+------------+-------------+---------------+-------------+
| Users.name | Places.name | Subjects.name | sum(length) |
+------------+-------------+---------------+-------------+
|   Burt Olm |           - |             - |           - |
|   Jane Doe |  Building B |          Math |         100 |
|          = |           = |       English |         120 |
|   John Doe |  Building A |          Math |         120 |
|          = |  Building B |          Math |          60 |
|          = |           = |       English |          30 |
+------------+-------------+---------------+-------------+

我尝试使用多个 GROUP BY ( Group by - multiple conditions - MySQL ) 在纯 SQL 查询中创建完整输出，但是当我执行 GROUP BY User.id,Places.id 时，它只显示每个用户一次(3 个结果)无论其他 GROUP BY 条件如何。

SQL:

SELECT PastLectures.id_users,Users.name AS user,Places.name AS places,Subjects.name AS subjects
  FROM PastLectures
  LEFT JOIN Users ON PastLectures.id_users = Users.id
  LEFT JOIN Places ON PastLectures.id_Places = Places.id
  LEFT JOIN Subjects ON PastLectures.id_Subjects = Subjects.id
  WHERE date >= \''.$monthStart->format('Y-m-d H:i:s').'\' AND date <= \''.$monthEnd->format('Y-m-d H:i:s').'\'
  GROUP BY Users.id,Places.id
  ORDER BY Users.name,Places.name,Subjects.name

但我不介意部分解决方案是用 PHP 完成的，我只是不知道下一步该怎么做。

编辑:

我还有一个表时间表，它存储谁定期教什么和在哪里。它仅存储使用过的表组合(每个有效组合一次)。

Timetable = lectures that regularly take place
+----+-----------+----------+------------+-------------+
| id | id_users  | id_place | id_subjects| other_stuff |
+----+-----------+----------+------------+-------------+
|  1 |         1 |        1 |          1 |           x |
|  2 |         1 |        2 |          1 |           y |
|  3 |         1 |        2 |          2 |           z |
|  4 |         2 |        2 |          1 |           a |
|  5 |         2 |        2 |          2 |           b |
+----+-----------+----------+------------+-------------+

是否可以仅添加具有在此表中有一行的组合的用户？

在这种情况下，这意味着省略 Burt Olm(时间表中没有 id=3)。但是，如果 Burt 有一个时间表条目但仍然没有 PastLectures 条目，他将在此处显示为示例结果(他应该在那个月有一个讲座，因为他在时间表中，但没有进行任何讲座)。

根据@Barmar 的解决方案，我更新了最终的 SQL，将 Timetable 设为主表并添加了一个 LEFT JOIN 以满足这些需求。

最终 SQL:

SELECT Users.name AS user,Places.name AS places,Subjects.name AS subjects, SUM(PastLectures.length)
  FROM Timetable
  LEFT JOIN PastLectures ON PastLectures.id_users = Timetable.id_users AND PastLectures.id_place = Timetable.id_place AND PastLectures.id_subjects = Timetable.id_subjects
    AND date BETWEEN '2015-11-01 00:00:00' AND '2015-11-30 23:59:59'
  LEFT JOIN Places ON Timetable.id_Place = Places.id
  LEFT JOIN Subjects ON Timetable.id_Subjects = Subjects.id
  LEFT JOIN Users ON Timetable.id_users = Users.id
  GROUP BY Timetable.id,Timetable.id_users,Timetable.id_Place,Timetable.id_Subjects
  ORDER BY Users.name,Places.name,Subjects.name

Answer 1

您需要在 GROUP BY 中包含 Subjects.id，以便为每个主题获得单独的结果。

此外，您不应在 GROUP BY 列中使用通过 LEFT JOIN 连接的表中的列。如果这样做，所有不匹配的行将被组合在一起，因为它们在该列中都具有 NULL。使用主表中的列。

GROUP BY PastLectures.id_users, PastLectures.id_Place, PastLectures.id_Subjects

DEMO

请注意，在演示输出中没有 Burt Olm 的行，因为他的所有行都被 WHERE 子句过滤掉了。如果您希望显示所有用户，您应该使 Users 成为主表，而不是 PastLectures。当加入 PastLectures 时，date 条件需要移到 ON 子句中。

SELECT Users.name AS user,Places.name AS places,Subjects.name AS subjects, SUM(length)
  FROM Users
  LEFT JOIN PastLectures ON PastLectures.id_users = Users.id 
    AND date BETWEEN '2015-11-01 00:00:00' AND '2015-11-30 23:59:59'
  LEFT JOIN Places ON PastLectures.id_Place = Places.id
  LEFT JOIN Subjects ON PastLectures.id_Subjects = Subjects.id
  GROUP BY Users.id, PastLectures.id_Place, PastLectures.id_Subjects
  ORDER BY Users.name,Places.name,Subjects.name

DEMO