mysql - 如果某个时间段之前和之后存在值，则创建日期列表

Question

我有下表，您也可以在 SQL Fiddle 中找到它 here :

CREATE TABLE Orders (
    Customer TEXT,
    Order_Date DATE
);

INSERT INTO Orders
(Customer, Order_Date)
VALUES 
("Customer A", "2017-05-23"),
("Customer A", "2019-01-03"),
("Customer A", "2019-02-15"),
("Customer A", "2019-02-16"),

("Customer B", "2018-09-10"),
("Customer B", "2019-01-09"),

("Customer C", "2016-09-04"),
("Customer C", "2019-02-12"),
("Customer C", "2019-02-20"),

("Customer D", "2017-03-15"),
("Customer D", "2019-02-17"),
("Customer D", "2019-02-19"),
("Customer D", "2019-02-20"),

("Customer E", "2019-02-03"),
("Customer E", "2015-10-12");

如您所见，表格显示了不同客户的订单日期。
我使用下面的 SQL 来获取客户的 unique count of orders:

a) placed an order in February 2019 and
b) did not place an order in the 12 month before and
c) placed an order before this 12 months period

引用答案here .

SELECT o.Customer,
       MAX( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) as num_feb_orders
FROM ORDERS o
GROUP BY o.Customer
HAVING SUM( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) > 0 AND
       SUM( o.Order_Date >= '2018-02-01' AND o.Order_Date < '2019-02-01' ) = 0 AND
       SUM( o.Order_Date < '2018-02-01' ) > 0 ;

到目前为止一切正常。

---

但是，现在我不想根据客户进行 GROUP BY，而是根据 Order_Date 进行 GROUP BY，所以所有2 月份的最新订单日期 应列出满足上述条件的客户。结果应如下所示。

Order_Date      UniqueOrders
2019-02-03            1       --> Customer E
2019-02-20            2       --> Customer C and Customer D

我需要在我的代码中更改什么才能使其正常工作？

Answer 1

希望这有助于我使用给定查询作为子查询来获得所需的输出，

SELECT OrderDate, 
    (COUNT(*) 
     FROM (
         SELECT o.Customer, MAX(o.Order_Date) AS OrderDate,
             MAX( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) AS UniqueOrders
         FROM ORDERS AS o
         GROUP BY o.Customer
        HAVING SUM( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) > 0 AND
           SUM( o.Order_Date >= '2018-02-01' AND o.Order_Date < '2019-02-01' ) = 0 AND
           SUM( o.Order_Date < '2018-02-01' ) > 0)a
GROUP BY OrderDate ;