mysql - 查询日期范围内的帮助，但不是最近的

Question

我知道这可以写成单个 SQL 语句，但我只是不知道该怎么做。我有两个单独的查询。 Ont 会提取去年特定时期的所有订单

SELECT * FROM `order` WHERE date_added BETWEEN '2014-10-01' AND '2014-11-01';

以及上个月的内容

SELECT * FROM `order` WHERE date_added BETWEEN DATE_SUB( now(), INTERVAL 1 MONTH) AND Now() ORDER BY date_added ASC

我现在想要做的是将两者连接起来，以便我只获取去年日期范围内下的订单的 customer_id(查询 1)，但上个月没有下订单(查询2)。

我知道有一种方法可以将其设置为连接，但我对 sql 连接的知识不是很有限。感谢您的帮助。

Answer 1

http://sqlfiddle.com/#!9/35ed0/1

SELECT * FROM `order` 
WHERE date_added BETWEEN '2014-10-01' AND '2014-11-01'
   AND customer_id NOT IN (
   SELECT DISTINCT customer_id  FROM `order` 
   WHERE date_added BETWEEN DATE_SUB( now(), INTERVAL 1 MONTH) AND Now())

更新 如果您只需要每个 customer_id 1 条记录，则为 an example 。从性能角度来看，这并不是最好的。但它仅返回每个客户的最后一个订单(根据 date_added 列)。

SELECT t.*,
   if(@fltr=customer_id, 0, 1) fltr,
   @fltr:=customer_id
FROM (SELECT *
  FROM `order` 
  WHERE date_added BETWEEN '2014-10-01' AND '2014-11-01'
    AND customer_id NOT IN (
     SELECT DISTINCT customer_id  FROM `order` 
     WHERE date_added BETWEEN DATE_SUB( now(), INTERVAL 1 MONTH) AND Now())
  ORDER BY customer_id, date_added DESC
) t
HAVING (fltr=1);