mysql - 在 join | 中显示空值销售值(value) - 类别

Question

SELECT
   cat_tbl.id_cat, 
   cat_tbl.name, 
   Sum(expences.price) AS PriceTotal
FROM cat_tbl
  JOIN  expences ON cat_tbl.id_kat= expences.category

GROUP BY
    cat_tbl.id_cat, 
    cat_tbl.name

结果

+------+--------------+--------+
| name | PriceTotal   | id_cat |
+------+--------------+--------+
| Cat1 |      1031.40 |      1 |
| Cat2 |       200.88 |      2 |
| Cat4 |        46.44 |      4 |
| Cat5 |       223.76 |      5 |
+------+--------------+--------+

问题是我有 4 个以上的类别，我尝试了不同的连接，但它们不会显示 PriceTotal 为空值的类别 1-7。

我真的不知道该怎么做

+------+--------------+--------+
| name | PriceTotal   | id_cat |
+------+--------------+--------+
| Cat1 |      1031.40 |      1 |
| Cat2 |       200.88 |      2 |
| Cat3 |         0    |      3 |
| Cat4 |        46.44 |      4 |
| Cat5 |       223.76 |      5 |
| Cat6 |       0      |      6 |
| Cat7 |       0      |      7 |
+------+--------------+--------+

Answer 1

左连接的 2 个选项:

SELECT
   cat_tbl.id_cat, 
   cat_tbl.name, 
   Sum(expences.price) AS PriceTotal
FROM cat_tbl
  LEFT JOIN  expences 
     ON cat_tbl.id_kat= expences.category
GROUP BY
    cat_tbl.id_cat, 
    cat_tbl.name

或:

select cat.id_cat, cat.name,
       coalesce(exp.tot, 0) as PriceTotal
from cat_tbl cat
left join
(
select x.category, sum(x.price) as tot
from expences x
group by x.category
) exp
on cat.id_cat = exp.category