不显示所有行的联接

Question

我有以下问题。

SELECT WEEK(Review.created)                               AS Week,
       CONCAT(Employee.firstname, ' ', Employee.lastname) AS Name,
       AVG(Rating.scale)                                  AS Average
FROM   employees Employee
       LEFT JOIN reviews Review
         ON Employee.id = Review.reviewee_id
       LEFT JOIN ratings AS Rating
         ON Rating.id = Review.rating_id
WHERE  Employee.id IN ( 71, 72 )
GROUP  BY WEEK(Review.created),
          Employee.id
ORDER  BY WEEK(Review.created),
          Employee.id 

结果如下所示：

36, Employee1, 2.9091
37, Employee2, 3.5000
37, Employee1, 3.7143
38, Employee2, 4.2000
38, Employee1, 4.0000
39, Employee2, 2.0000
40, Employee2, 2.8333
40, Employee1, 3.8571
41, Employee1, 2.6667
43, Employee2, 2.5000
43, Employee1, 1.5714
44, Employee2, 3.8333
44, Employee1, 4.4000
45, Employee2, 3.2500
45, Employee1, 4.8571
46, Employee2, 2.1667
46, Employee1, 2.2000
48, Employee2, 2.6667
49, Employee2, 1.4000
49, Employee1, 3.5000
50, Employee2, 2.0000
50, Employee1, 1.5000
51, Employee2, 2.7143
51, Employee1, 2.7500

我需要的是返回员工，即使他们没有该周的评级。

因此，前两行将返回

36, Employee1, 2.9091
36, Employee2, NULL

在这方面的任何帮助都将非常感谢。

Answer 1

可能是因为查询中的分组导致您的结果被省略。尝试删除分组，看看会发生什么情况

来源

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Answer 2

将group by子句更改为

GROUP BY Employee.id, WEEK(Review.created)

按照现在的情况，如果您在考核周对第一个分组，则在该周没有考核的任何员工都将首先在NULL (week(null) -> null)分组。