SQL连接问题，如果第二个表为空，则返回null。

Question

我有2个表。

表1 (projects)：id，名称，类型

表2 (project_ratings)：project_id，评分

一些项目没有评分。

SELECT `p`.`id`, `p`.`name`, AVG(pr.rating) FROM (`projects` p) JOIN `project_ratings` pr ON `p`.`id` = `pr`.`project_id` WHERE `p`.`type` = 'group' OR `p`.`type` = 'user';

我希望返回所有项目，如果没有评级则返回NULL。这个查询只返回有评级的项目。

我尝试了左连接、右连接、全连接，但结果都一样。

使用CodeIgniter Active Records：

$this->db->select("p.id, p.name, AVG(pr.rating)");
        $this->db->from('projects p');
        $this->db->join('project_ratings pr', 'p.id = pr.project_id');

        $this->db->where('p.type', 'group');
        $this->db->or_where('p.type', 'user');
$res = $this->db->get();

我错过了什么吗？

- tpae

---

这是“LEFT JOIN”的定义，但我认为你使用“AVG”聚合函数不适用于此。 - Phil

我尝试了左连接...仍然只返回1个结果..我想要全部。 - tpae

我有一种感觉你需要一个 GROUP BY p.id, p.name，但我仍然认为第三列不会显示正确的值。我可能错了，但懒得自己测试。 - Phil

GROUP BY起了作用..如果你能把它作为一个完整的答案回答，我可以给你信用:)谢谢 - tpae

连同左外连接。忘记添加了。 - tpae

@tpae 请查看下面的答案：https://dev59.com/RFbUa4cB1Zd3GeqPB8ln#5864448 - Phil

Answer 1

你所需要的SQL语句应该是：

         SELECT *
           FROM projects
LEFT OUTER JOIN project_ratings ON projects.id = project_ratings.project_id

我不确定如何在CodeIgniter中完成这个操作。

- Stefan H

Answer 2

使用聚合函数（在本例中为AVG()），需要在非聚合字段上指定GROUP BY子句，例如：

GROUP BY p.id, p.name

为了确保项目引用在连接评级之后也能存在，应使用 LEFT JOIN。

- Phil

Answer 3

试试这个：

$this->db->select("p.id, p.name, AVG(pr.rating) as average_rating");
$this->db->from('projects p');
$this->db->join('project_ratings pr', 'p.id = pr.project_id', 'left');
$this->db->where('p.type', 'group');
$this->db->group_by('p.id');
$this->db->or_where('p.type', 'user');
$res = $this->db->get();

如果没有评分，average_rating 将为 NULL。

- Mike Neumegen

Answer 4

查询应该是：

SELECT `p`.`id`, `p`.`name`, AVG(pr.rating) 
FROM (`projects` p) 
    LEFT OUTER JOIN `project_ratings` pr ON `p`.`id` = `pr`.`project_id` 
WHERE `p`.`type` = 'group' OR `p`.`type` = 'user';

（在 CodeIgniter 中，我不知道该如何做到这一点 :）

- morgar

Answer 5

你可以通过以下方式获取外连接：

    $this->db->join('project_ratings pr', 'p.id = pr.project_id', 'outer');

- Richard Schneider