mysql - 内连接 : add up amounts with same id

Question

如果客户 ID (KUNDENID) 相同，我将尝试从表中添加金额。

这是我的两个表:

+----------+------------+--------+
| KUNDENID | datum      | Betrag |
+----------+------------+--------+
|        2 | 2013-06-05 |  120.5 |
|        1 | 2013-02-05 |  20.95 |
|        3 | 2013-02-05 |  250.3 |
|        3 | 2013-05-13 |    100 |
|        4 | 2013-01-01 |   1500 |
+----------+------------+--------+

+----------+---------+-----------+------------------------------+--------------+-------------+
| KUNDENID | vorname | nachname  | email                        | geburtsdatum | telefon     |
+----------+---------+-----------+------------------------------+--------------+-------------+
|        1 | Homer   | Simpson   | chunkylover53@aol.com        | 1956-05-02   | 555-364     |
|        2 | Doug    | Heffernan | doug.heffernan@hotmail.com   | 1965-02-09   | 555-3684    |
|        3 | Dexter  | Morgan    | dexter.morgan@gmail.com      | 1971-02-01   | 555-3684    |
|        4 | Sheldon | Cooper    | sheldoncooper@gmail.com      | 1981-04-22   | 555-2274648 |
|        5 | Maurice | Moss      | moss@Reynholm-Industries.com | 1972-03-01   | 555-6677    |
+----------+---------+-----------+------------------------------+--------------+-------------+

我尝试了这个 INNER JOIN:

SELECT k.KUNDENID, vorname, nachname, SUM(Betrag) FROM kunde k INNER JOIN verkauf v ON k.KUNDENID = v.KUNDENID GROUP BY k.KUNDENID;

输出:

+----------+---------+-----------+------------------+
| KUNDENID | vorname | nachname  | SUM(Betrag)      |
+----------+---------+-----------+------------------+
|        1 | Homer   | Simpson   | 20.9500007629395 |
|        2 | Doug    | Heffernan |            120.5 |
|        3 | Dexter  | Morgan    | 350.300003051758 |
|        4 | Sheldon | Cooper    |             1500 |
+----------+---------+-----------+------------------+

所以我知道 JOIN 是正确的，但我如何向未在同一个 JOIN 中购买任何东西的客户显示？

我想要的输出:

+----------+---------+-----------+------------------+
| KUNDENID | vorname | nachname  | SUM(Betrag)      |
+----------+---------+-----------+------------------+
|        1 | Homer   | Simpson   | 20.9500007629395 |
|        2 | Doug    | Heffernan |            120.5 |
|        3 | Dexter  | Morgan    | 350.300003051758 |
|        4 | Sheldon | Cooper    |             1500 |
|        5 | Maurice | Moss      |                0 |
+----------+---------+-----------+------------------+

感谢您的帮助!

Answer 1

使用 LEFT JOIN 而不是 INNER JOIN。

SELECT k.KUNDENID, vorname, nachname, IFNULL(SUM(Betrag), 0) AS gesamt
    FROM kunde k
    LEFT JOIN verkauf v ON k.KUNDENID = v.KUNDENID
    GROUP BY k.KUNDENID;

这是 Wikipedia的基本解释。他们也展示了一个例子。

The result of a left outer join (or simply left join) for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B (for a given record in A), the join will still return a row in the result (for that record)—but with NULL in each column from B. A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table.