我有以下简单表,您也可以在SQL Fiddle here中找到该表:
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.
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既列出满足上述条件的客户,又计算该客户在2019年2月下的订单数量:
在上面的示例中,我期望得到以下结果:
Customer C 2
要实现此目的,我需要更改查询中的哪些内容?