MySQL CASE ELSE 在多连接选择语句中不返回 0

Question

我有这样的观点:

VIEW `my_view` AS (
SELECT 
    t1.sku AS sku,
    FORMAT(SUM((CASE
            WHEN (t3.order_date > (CURDATE() - INTERVAL 42 DAY)) THEN t2.qty
            ELSE 0
        END)),
        0) AS qty_sold,
    FORMAT(SUM((CASE
            WHEN (t3.order_status_id = 3) THEN t2.qty
            ELSE 0
        END)),
        0) AS qty_ordered
FROM
    inventory_products t1
    JOIN inventory_sales t2 ON (t2.sku = t1.sku)
    JOIN inventory_orders t3 ON (t3.id = t2.order_id)
where t1.sku=1001 or t1.sku=1002 or t1.sku=1010
    GROUP BY t1.sku
)

该 View 按预期工作，并返回正确的结果，但当 sku 时除外inventory_sales 上不存在表中，该 sku 的行根本不会返回。

请注意 sku=1010 inventory_sales 上不存在表，但它确实存在于 inventory_products 上表。

例如:

SELECT * FROM my_view 
WHERE sku=1001 OR sku=1002 OR sku=1010

返回:

+-----------+--------------+------------+
|    sku    |   qty_sold   |qty_ordered |   
+-----------+--------------+------------+
|   1001    |      2       |     2      |
+-----------+--------------+------------+
|   1002    |      3       |     3      |
+-----------+--------------+------------+

但我需要它返回这个:

+-----------+--------------+------------+
|    sku    |   qty_sold   |qty_ordered |   
+-----------+--------------+------------+
|   1001    |      2       |     2      |
+-----------+--------------+------------+
|   1002    |      3       |     3      |
+-----------+--------------+------------+
|   1010    |      0       |     0      |
+-----------+--------------+------------+

我在这里缺少什么？任何建议将不胜感激:)

Answer 1

我认为你只需要左连接:

SELECT . . .
FROM inventory_products t1 LEFT JOIN
     inventory_sales t2
     ON t2.sku = t1.sku LEFT JOIN
     inventory_orders t3
     ON t3.id = t2.order_id
WHERE t1.sku IN (1001, 1002, 1010)
GROUP BY t1.sku

我认为ELSE 0会将NULL变成0——你想要的结果。