mysql - 3 表连接计数空值

Question

我有一个情况，我有三个表；经销店、汽车和仓库。经销商与汽车相关，汽车与仓库相关。如何构建 mysql 查询来确定仓库中未发送给经销商的车辆数量之和？

以下是数据表:

Dealer
Id   |   name     |                           
_____|____________|
D1   |  Dealer_1  |
D2   |  Dealer_2  |
D3   |  Dealer_3  |
D4   |  Dealer_4  |


Car
Id   |    name   | Dealer_id |
_____|___________|___________|
C1   |  auto_1|  |   D4      |
C2   |  auto_2   |   D4      |
C3   |  auto_3   |   null    |
C4   |  auto_4   |   D1      |
C5   |  auto_5   |   null    |
C6   |  auto_6   |   D3      |


Warehouse
Id   |    name   | Car_id    | vehical_count |
_____|___________|___________|_______________|
W1   | storage_1 |   C2      |   22          |
W2   | storage_2 |   C3      |   43          |
W3   | storage_3 |   C5      |   18          |
W4   | storage_4 |   C6      |   15          |
W5   | storage_5 |   C1      |    8          |
W6   | storage_6 |   null    |    2          |
W7   | storage_7 |   C3      |   37          |
W8   | storage_8 |   null    |   10          |

谢谢!

Answer 1

这应该为您提供不在汽车表中或在没有分配经销商的汽车表中的仓库汽车。

SELECT
    SUM(w.vehical_count)
FROM
    Warehouse w
    LEFT OUTER JOIN Car c ON c.Id=w.Car_id
WHERE
    c.Dealer_id IS NULL