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

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

概述我有下表,您也可以在SQL Fiddle here中找到该表:CREATE TABLE Orders ( Customer TEXT, Order_Date DATE ); INSERT INTO Orders (Customer, Order_Date) VALUES ('Customer A', '2017-05-23'), ('Cu

我有下表,您也可以在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-如果值在特定时间段之前和之后存在,则创建日期列表 所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/1166008.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-06-01
下一篇 2022-06-01

发表评论

登录后才能评论

评论列表(0条)

保存