mysql - 选择与另外两个表有多个连接的行

Question

假设我有三个表:

table `customers` with columns `id`,`name`
table `contracts` with columns `id`, `customer_id`, `text`
table `phones` with columns `id`, customer_id`, `number`

我想选择拥有超过 1 个契约(Contract)和多个电话号码的客户。我尝试执行以下操作:

SELECT * FROM `customers` 

WHERE 

 (SELECT count(*) FROM `contracts` WHERE `customer_id` = `id`) > 1 
  AND 
 (SELECT count(*) FROM `phones` WHERE `customer_id` = `id`) > 1

但它会产生错误 #1054 - 'where clause' 中的未知列 'customer_id'

Answer 1

请尝试以下查询:

SELECT customers.* FROM customers

INNER JOIN
(

  SELECT customer_id, count(*) AS count FROM contracts GROUP BY customer_id 

  HAVING count > 1

) AS contracts

ON customers.id = contracts.customer_id

INNER JOIN

(

  SELECT customer_id, count(*) AS count FROM phones GROUP BY customer_id 

  HAVING count > 1

) AS phones

ON customers.id = phones.customer_id