MySQL分组查询

Question

我的数据库里有很多表。

表: ObjectToPerson

例如，如果我在数据库中有以下几个条目：

+----+------------+------------+----------+----------+--------------+
| Id | WeekNumber |    Date    | PersonId | ObjectId | ObjectTypeId |
+----+------------+------------+----------+----------+--------------+
|  1 |          1 | 2015-11-04 |        1 |        1 |            1 |
|  2 |          1 | 2015-11-04 |        1 |        3 |            2 |
|  3 |          1 | 2015-11-04 |        2 |        2 |            1 |
|  4 |          1 | 2015-11-04 |        2 |        4 |            2 |
+----+------------+------------+----------+----------+--------------+

我想将结果返回如下两行：

+------+------------+----------+----------------------------+----------------------------+
| Week |    Date    | PersonId | ObjectId(ObjectTypeId = 1) | ObjectId(ObjectTypeId = 2) |
+------+------------+----------+----------------------------+----------------------------+
|    1 | 2015-11-04 |        1 |                          1 |                          3 |
|    1 | 2015-11-04 |        2 |                          2 |                          4 |
+------+------------+----------+----------------------------+----------------------------+

我正在考虑某种按查询分组的方法，但我似乎无法正确地理解它。

Select * From ObjectToPerson
Left Join Objects O On O.Id = ObjectToPerson.ObjectId And ObjectToPerson.ObjectTypeId = 1
Left Join Objects O On O.Id = ObjectToPerson.ObjectId And ObjectToPerson.ObjectTypeId = 2

有人能解释一下我是怎么做到的吗？

Answer 1

如果列的类型是正确的，可以用CASE只选择ObjectId，然后使用MAX/GROUP将结果分组为每个人/周/日期一个行。

SELECT WeekNumber week, date, personid, 
  MAX(CASE WHEN ObjectTypeId=1 THEN ObjectId END) Type1,
  MAX(CASE WHEN ObjectTypeId=2 THEN ObjectId END) Type2
  FROM ObjectToPerson
  GROUP BY week, date, personid

一个需要测试的SQLfiddle。

Answer 2

您不需要两个联接，您需要一个WHERE子句；

SELECT * FROM ObjectToPerson
LEFT JOIN Objects O ON O.Id = ObjectToPerson.ObjectId
WHERE ObjectToPerson.ObjectTypeId IN(1,2)