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;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!