我有以下简单的表格,您也可以在SQL Fiddle
here http://www.sqlfiddle.com/#!9/74d8fb/3:
CREATE TABLE Orders (
Customer TEXT,
Order_Date DATE
);
INSERT INTO Orders
(Customer, Order_Date)
VALUES
("Customer A", "2017-05-23"),
("Customer B", "2018-09-10"),
("Customer B", "2018-12-14"),
("Customer A", "2019-01-03"),
("Customer A", "2019-02-15"),
("Customer C", "2017-09-04"),
("Customer B", "2019-01-09"),
("Customer A", "2019-02-16"),
("Customer C", "2019-02-12"),
("Customer C", "2019-02-15"),
("Customer B", "2018-01-03");
如您所见,该表显示了不同客户的订单日期。
我使用下面的 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 https://stackoverflow.com/questions/54872070/select-values-which-exist-before-and-after-a-certain-time-period .
SELECT o.Customer
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-01-02' AND o.Order_Date < '2019-02-01' ) = 0 AND
SUM( o.Order_Date < '2018-01-02' ) > 0 ;
到目前为止,这一切都运行良好。
但是,现在我想实现 SQL 既列出满足上述条件的客户,又列出counts
客户下的订单数量February 2019
:
在上面的例子中,我期望得到以下结果:
Customer C 2
我需要在查询中更改哪些内容才能实现此目的?