php - 指定没有订单的客户

Question

我有一个像这样的客户表

customers_id   |   customers_date_added
-----------------------------
   1           | 2012-10-13 09:31:38
   2           | 2013-12-13 08:31:38
   3           | 2014-09-13 10:31:38
   4           | 2013-08-13 09:31:38
   5           | 2011-07-13 14:31:38

和这样的订单表

   orders_id   |   customers_id
-------------------------------
     1         |      2
     2         |      3
     3         |      5
     4         |      5
     5         |      2

我尝试获取2012年和2013年没有订单并注册的客户。在这种情况下，我想获取customers_id 1和4。

这不起作用。

SELECT customers_id
FROM customers
WHERE  customers_id NOT IN (
  SELECT DISTINCT customers_id
  FROM   orders
)
AND EXTRACT(YEAR FROM customers_date_added) = '2013'
OR  EXTRACT(YEAR FROM customers_date_added) = '2012'

我的查询有什么问题？

Answer 1

为什么不这样使用 LEFT JOIN:

SELECT customers.customers_id
FROM customers
LEFT JOIN order ON customers.customers_id = order.customers_id
WHERE order.customers_id IS NULL
AND customers.customers_date_added BETWEEN '2012-01-01 00:00:00' AND '2013-12-31 23:59:59'