mysql-如果值在特定时间段之前和之后存在,则创建日期列表

我有下表,您也可以在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"),"2019-02-15"),"2019-02-16"),("Customer B","2018-09-10"),"2019-01-09"),("Customer C","2016-09-04"),"2019-02-12"),"2019-02-20"),("Customer D","2017-03-15"),"2019-02-17"),"2019-02-19"),("Customer E","2019-02-03"),"2015-10-12");

如您所见,该表显示了来自不同客户的订单日期.
我使用以下sql来获得客户的唯一订单数：

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.结果应如下所示.

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

我需要在代码中进行哪些更改才能使其正常工作？

最佳答案

您可以只使用子查询：

SELECT last_feb_order_date,COUNT(*) as num_customers
FROM (SELECT o.Customer,MAX( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) as num_feb_orders,MAX(CASE WHEN o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' THEN o.Order_Date END) as last_feb_order_date
      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
     ) oc
GROUP BY last_feb_order_date
ORDER BY last_feb_order_date;

原文链接：https://www.f2er.com/mysql/531920.html

mysql-如果值在特定时间段之前和之后存在,则创建日期列表

猜你在找的MySQL相关文章