我有下表,您也可以在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 andb) dID not place an order in the 12 month before andc) 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_ordersFROM ORDERS oGROUP BY o.CustomerHAVING 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 UniqueOrders2019-02-03 1 --> Customer E2019-02-20 2 --> Customer C and Customer D
我需要在代码中进行哪些更改才能使其正常工作?最佳答案您可以只使用子查询:
SELECT last_feb_order_date,COUNT(*) as num_customersFROM (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 ) ocGROUP BY last_feb_order_dateORDER BY last_feb_order_date;
总结 以上是内存溢出为你收集整理的mysql-如果值在特定时间段之前和之后存在,则创建日期列表 全部内容,希望文章能够帮你解决mysql-如果值在特定时间段之前和之后存在,则创建日期列表 所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)