sql - 什么是正确的 SQL 语句来实现以下结果

Question

我有下表。

Owner_id    Owner
-----------------
1           Bill
2           Steve


Animal      Owner_fk_id
-----------------------
Cat         1
Dog         1
Lion        2

Car         Owner_fk_id
-----------------------
Ferrari     1
BMW         1
Lotus       2

我希望为拥有至少 1 辆法拉利的车主列出所有动物和汽车。由于 Bill 有一辆法拉利，我们将列出 Bill 的所有汽车和动物。

如果我使用以下 SQL:

SELECT *
    FROM Owner
    LEFT JOIN Animal ON (Animal.Owner_fk_id = Owner.Owner_id )
    LEFT JOIN Car ON (Car.Owner_fk_id = Owner.Owner_id )
    WHERE Car.Car = 'Ferrari'

我会得到

Owner       Animal  Car
---------------------------
Bill        Cat     Ferrari
Bill        Dog     Ferrari

我想要的结果是什么

Owner       Animal  Car
---------------------------
Bill        Cat     Ferrari
Bill        Cat     BMW
Bill        Dog     Ferrari
Bill        Dog     BMW

我可以知道我应该使用的正确 SQL 语句是什么吗？

Answer 1

根据您需要的条件(即 Car='Ferrari)进行内部联接，然后像以前一样左联接其他表。

SELECT o.Owner, a.Animal, c2.Car
FROM Owner as o
INNER JOIN Car as c1 on c1.Owner_fk_id = o.Owner_id and c1.Car='Ferrari'
LEFT JOIN Animal as a ON a.Owner_fk_id = o.Owner_id
LEFT JOIN Car as c2 ON c2.Owner_fk_id = o.Owner_id;