mysql SUM 和 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

Answer 2

试试这个：

  SELECT a.*,  
    SUM(Case B.Active When 1 Then b.cost else 0 End) AS total_cost 
    FROM categories a 
       LEFT JOIN items b 
         ON b.category_id = a.id  
    GROUP BY a.category_name 

或者这个：

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