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