mysql left join 问题与 SU​​M 和 WHERE 子句

Question

我的数据库中有 2 个表:项目和类别。项目可以是事件的或非事件的，并且具有与类别表中记录的 ID 相关的类别 ID。

我想执行查询以显示所有类别，以及该类别的有效项目的总成本

所以我的目标是返回如下所示的内容:

    +--------+------------+---------------+
    | id     | cat_name   | total_cost    |
    +--------+------------+---------------+
    |      1 | cat 1      | 12            |
    |      2 | cat 2      | 0             |
    |      3 | cat 3      | 45            |
    +--------+------------+---------------+

我的第一个查询:

    SELECT a.*, 
    SUM(b.cost) AS total_cost
    FROM categories a LEFT JOIN items b
    ON(a.id = b.category_id)
    GROUP BY a.category_name

工作正常，但它返回 NULL 项目而不是 0，并使用所有项目，无论事件/非事件:

    +--------+------------+---------------+
    | id     | cat_name   | total_cost    |
    +--------+------------+---------------+
    |      1 | cat 1      | 44            |
    |      2 | cat 2      | NULL          |
    |      3 | cat 3      | 87            |
    +--------+------------+---------------+

我的第二个查询处理 NULL 值:

    SELECT a.*, 
    SUM(IF(b.cost IS NULL, 0, b.cost)) AS total_cost
    FROM categories a LEFT JOIN items b
    ON(a.id = b.category_id)
    GROUP BY a.category_name

结果是这样的:

    +--------+------------+---------------+
    | id     | cat_name   | total_cost    |
    +--------+------------+---------------+
    |      1 | cat 1      | 44            |
    |      2 | cat 2      | NULL          |
    |      3 | cat 3      | 87            |
    +--------+------------+---------------+

所以在我小小的无用大脑中，我尝试了以下查询，在表 b 上添加一个 WHERE 子句，其中 active 必须 = 1 (true)

    SELECT a.*, 
    SUM(IF(b.cost IS NULL, 0, b.cost)) AS total_cost
    FROM categories a LEFT JOIN items b
    ON(a.id = b.category_id)
    WHERE b.active = 1 
    GROUP BY a.category_name

我得到以下信息:

    +--------+------------+---------------+
    | id     | cat_name   | total_cost    |
    +--------+------------+---------------+
    |      1 | cat 1      | 12            |
    |      3 | cat 3      | 45            |
    +--------+------------+---------------+

如您所见，我想返回整个类别范围，即使右侧表格未返回匹配结果...是否需要一百万个假想的酷点？

Answer 1

使用:

   SELECT c.id,
          c.cat_name,
          COALESCE(SUM(i.cost), 0) AS total_cost
     FROM CATEGORIES c
LEFT JOIN ITEMS i ON i.category_id = c.category_id
                 AND i.active = 1
 GROUP BY c.id, c.cat_name