mysql - 如何将第三个条件放入 mysql 查询中

Question

在生产商至少拥有 10 辆已售汽车的条件下，如何实现此查询？

SELECT
    *
FROM
    `car`
INNER JOIN
    producer ON car.producer = producer.id
INNER JOIN
    owner ON car.owner = owner.id
WHERE
    owner.email IS NOT NULL
AND
    producer.email IS NOT NULL
AND
    owner.birth_date >= car.produce_year

这三个表是:

TABLE car
id integer
owner integer
color varchar
type varchar 
producer integer
poduce_time integer

TABLE owner
id integer
first name varchar
last name varchar
address varchar
date_of_birth integer
place_of_birth varchar
email varchar

TABLE producer
id integer
name varchar
address varchar
email varchar

Answer 1

您必须使用COUNT:

SELECT
    *
FROM
    car c
INNER JOIN
    producer ON c.producer = producer.id
INNER JOIN
    owner ON c.owner = owner.id
WHERE
    owner.email IS NOT NULL
AND
    producer.email IS NOT NULL
AND
    owner.birth_date >= c.produce_year
AND 
    (SELECT COUNT(*) FROM car WHERE car.producer = c.producer) >= 10