mysql - SQL - 如何解决需要术语 'ONLY' 的查询？

Question

customer(Id, Name, Address, PhoneNumber);
station (Id, City, Country, Location)
# e.g.: (123, ”London”, ”UK”, ”Stansted Airport”);
car(Id, Reg, Type, Milage);
contract(CustId, StationId, CarId);

查询1:选择仅租用宝马的所有客户(姓名和联系电话)。

我的查询:

SELECT Name, PhoneNumber 
FROM Car, Customer, Station, Contract 
WHERE customer.Id = CustId AND station.Id = StationId AND Car.Id = CarId AND Type = "BMW" 
GROUP BY Name HAVING COUNT(Distinct Type) = 1;

查询2:所有租过BMW和Aud的客户

Answer 1

您可以首先使用 HAVING 子句查找仅租用 BMW 的客户 ID，然后将其与客户表连接以获取这些客户 ID 的相关详细信息。

试试这个:

select c.name,
    c.phonenumber
from customer c
join (
    select c1.custId
    from Contract c1
    join Car c2 on c1.carId = c2.id
    group by c1.custId
    having count(distinct c2.Type) = 1
        and sum(c2.type = 'BMW') > 0
    ) t on c.id = t.custId;

这里:

• count(distinct c2.Type) = 1 表示该客户 ID 只有一种类型的汽车。
• sum(c2.type = 'BMW') > 0 表示客户 ID 至少有一辆“BMW”类型的汽车