php - Mysql 查询从没有记录的连接计数为零

Question

我有一些特定的数据库表用于下面的商店:

用户表

+----------+
|id| name  |
+--+-------+
|1 | Mr. A |
|2 | Mr. B |
|3 | Mr. C |
+--+-------+

类别项目

+--+-------+
|id| name  |
+--+-------+
|1 | Cat 1 |
|2 | Cat 2 |
|3 | Cat 3 |
+--+-------+

项目表

+---+-------------+--------+-------+
|id | category_id | title  | price |
+---+-------------+--------+-------+
|1  |1            | title 1|10     |
|2  |1            | title 2|5      |
|3  |1            | title 3|20     |
|4  |2            | title 4|10     |
|5  |2            | title 5|15     |
|6  |3            | title 6|30     |
+---+-------------+--------+-------+

交易表

+---+--------+--------+---------+------------+----------+
|id | user_id| item_id|buy_value|buy_at_price| date     |
+---+--------+--------+---------+------------+----------+
|1  |1       | 1      |5        |10          |2018-01-01|
|2  |1       | 3      |2        |20          |2018-01-01|
|3  |1       | 3      |1        |20          |2018-01-01|
|4  |2       | 4      |2        |10          |2018-01-01|
|5  |2       | 5      |2        |15          |2018-01-02|
+---+--------+--------+---------+------------+----------+

帮助我执行一个 mysql 查询，该查询可以按类别进行每日销售回顾。此类回顾的示例会生成如下表格(如果在 2018 年 1 月 1 日进行回顾)。

查询结果应为:

+-------------+--------------+--------------------------+-----------------+
| category_id | category_name| total_category_buy_value |category_income  |
+-------------+--------------+--------------------------+-----------------+
|1            | Cat 1        |8                         |110              |
|2            | Cat 2        |2                         |20               |
|3            | Cat 3        |0                         |0                |
+---+-------------+----------+--------------------------+-----------------+

但是，我的查询没有显示 Cat 3。这是我的查询

SELECT
    category.id AS category_id,
    category. NAME AS category_name,
    sum(`transaction`.buy_value) AS total_category_buy_value,
    sum(
        `transaction`.buy_value * `transaction`.buy_at_price
    ) AS total_income
FROM
    category
JOIN item ON item.category_id = category.id
JOIN `transaction` ON `transaction`.item_id = item.id
WHERE
    `transaction`.date LIKE '2018-01-01%'
GROUP BY
    category_id

请访问http://sqlfiddle.com/#!9/ce4b1/1/0

谢谢

Answer 1

要获取所有类别，无论其是否有交易(通过项目)或没有任何交易数据，您可以使用 LEFT 连接到项目和交易表。

SELECT
    c.id AS category_id,
    c.name AS category_name,
    COALESCE(SUM(t.buy_value),0) AS total_category_buy_value,
    COALESCE(SUM(t.buy_value * t.buy_at_price),0) AS total_income
FROM
    category c
LEFT JOIN item i ON i.category_id = c.id
LEFT JOIN `transaction` t ON t.item_id = i.id 
                          AND t.date LIKE '2018-01-01%' 
GROUP BY c.id,c.name

在上面，您还可以注意到我已将 ON 子句中的日期过滤器从 WHERE 移出，因此不会将整个交易表连接到与此过滤器匹配的数据上将被连接，如果您在 WHERE 中使用此子句，它将把外连接转换为内连接

DEMO